Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

verify_password_function option

Implements password rules.

Allowed values

String

Default

Empty string

Scope
  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION
Allowed to set temporarily? Yes, with SET ANY SECURITY OPTION Yes (current connection only), with SET ANY SECURITY OPTION No
Remarks

If a password is set, no function is called if the value is an empty string.

The function specified by this option is called automatically when a non-NULL password is created or set. The function specified is not called for users that have a login policy with the change_password_dual_control option enabled. To prevent a user from overriding the function, set the option value to owner.function-name. A user must have a password to connect to the database.

After validating the statement used to create or set the password, the function is called to verify the password using the specified rules. If the password conforms to the specified rules, the function returns NULL to indicate success, and the invoking statement executes. Otherwise a non-NULL string is returned, and is cited as the reason for the error.

The password verification function takes two parameters: user-name VARCHAR(128) and new-pwd VARCHAR(255). It returns a value of type VARCHAR(255).

Execute an ALTER FUNCTION function-name SET HIDDEN statement on the password verification function to ensure that it cannot be stepped through using the debugger.

If the verify_password_function option is set, specifying more than one user ID and password with a GRANT CONNECT statement is not allowed.

Example

The following example defines a table and a function and sets some login policy options. Together they implement advanced password rules that include requiring certain types of characters in the password, disallowing password re-use, and expiring passwords. The function is called by the database server with the verify_password_function option when a user ID is created or a password is changed. The application can call the procedure specified by the post_login_procedure option to report that the password should be changed before it expires.

The code for this sample is also available in the following location: %SQLANYSAMP17%\SQLAnywhere\SQL\verify_password.sql.

-- Only a database administrator should have permissions on this table.
CREATE TABLE IF NOT EXISTS DBA.t_pwd_history(
        pk          INT         DEFAULT AUTOINCREMENT PRIMARY KEY,
        user_name   CHAR(128),          -- the user whose password is set
        pwd_hash    CHAR(32) );         -- hash of password value to detect
                                        -- duplicate passwords

-- Called whenever a non-NULL password is set to verify that the password 
-- conforms to password rules.
CREATE OR REPLACE FUNCTION DBA.f_verify_pwd( uid     VARCHAR(128),
                                             new_pwd VARCHAR(255) )
RETURNS VARCHAR(255)
BEGIN
    -- a table with one row per character in new_pwd
    DECLARE local temporary table pwd_chars(
            pos INT PRIMARY KEY,    -- index of c in new_pwd
            c   CHAR( 1 CHAR ) );   -- character
    -- new_pwd with non-alpha characters removed
    DECLARE pwd_alpha_only      CHAR(255);
    DECLARE num_lower_chars     INT;

    -- enforce minimum length (can also be done with
    -- min_password_length option)
    IF length( new_pwd ) < 6 THEN
        RETURN 'password must be at least 6 characters long';
    END IF;

    -- break new_pwd into one row per character
    INSERT INTO pwd_chars SELECT row_num, substr( new_pwd, row_num, 1 )
                            FROM dbo.RowGenerator
                            WHERE row_num <= length( new_pwd );

    -- copy of new_pwd containing alpha-only characters
    SELECT list( c, '' ORDER BY pos ) INTO pwd_alpha_only
        FROM pwd_chars WHERE c BETWEEN 'a' AND 'z' OR c BETWEEN 'A' AND 'Z';

    -- number of lower case characters IN new_pwd
    SELECT count(*) INTO num_lower_chars
        FROM pwd_chars WHERE CAST( c AS BINARY ) BETWEEN 'a' AND 'z';

    -- enforce rules based on characters contained in new_pwd
    IF ( SELECT count(*) FROM pwd_chars WHERE c BETWEEN '0' AND '9' )
           < 1 THEN
        RETURN 'password must contain at least one numeric digit';
    ELSEIF length( pwd_alpha_only ) < 2 THEN
        RETURN 'password must contain at least two letters';
    ELSEIF num_lower_chars = 0
           OR length( pwd_alpha_only ) - num_lower_chars = 0 THEN
        RETURN 'password must contain both upper- and lowercase characters';
    END IF;

    -- not the same as any user name
    -- (this could be modified to check against a disallowed words table)
    IF EXISTS( SELECT * FROM SYS.SYSUSER
                    WHERE lower( user_name ) IN ( lower( pwd_alpha_only ),
                                                  lower( new_pwd ) ) ) THEN
        RETURN 'password or only alphabetic characters in password ' ||
               'must not match any user name';
    END IF;

    -- not the same as any previous password for this user
    IF EXISTS( SELECT * FROM t_pwd_history
                    WHERE user_name = uid
                      AND pwd_hash = hash( uid || new_pwd, 'md5' ) ) THEN
        RETURN 'previous passwords cannot be reused';
    END IF;

    -- save the new password
    INSERT INTO t_pwd_history( user_name, pwd_hash )
        VALUES( uid, hash( uid || new_pwd, 'md5' ) );

    RETURN( NULL );
END;

ALTER FUNCTION DBA.f_verify_pwd SET HIDDEN;
GRANT EXECUTE ON DBA.f_verify_pwd TO PUBLIC;
SET OPTION PUBLIC.verify_password_function = 'DBA.f_verify_pwd';

-- All passwords expire in 180 days.  Expired passwords can be changed
-- by the user using the NewPassword connection parameter.
ALTER LOGIN POLICY root password_life_time = 180;

-- If an application calls the procedure specified by the post_login_procedure
-- option, then the procedure can be used to warn the user that their
-- password is about to expire. 
ALTER LOGIN POLICY root password_grace_time = 30;

-- Five consecutive failed login attempts will result in a userid being locked.
ALTER LOGIN POLICY root max_failed_login_attempts = 5;