verify_password_function option [database]

Use the verify_password_function option to implement password rules.

Allowed values

String

Default

Empty string (no function is called when a password is set).

Scope

DBA authority required.

Remarks

The function specified by the verify_password_function is called automatically when a non-NULL password is created or set.
To prevent a user from overriding the function, set the option value to owner.function-name. A user must have a password to be able to connect to the database. Passwords are case sensitive and they cannot:

begin with white space, single quotes, or double quotes

end with white space

contain semicolons

be longer than 255 bytes in length

When passwords are created or changed, they are converted to UTF-8 before being hashed and stored in the database. If the
database is unloaded and reloaded into a database with a different character set, existing passwords continue to work. If
the database server cannot convert from the client's character set to UTF-8, then it is recommended that the password be composed
of 7-bit ASCII characters as other characters may not work correctly.

You can use any of the following statements to set a password:

CREATE USER

ALTER USER

GRANT

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 must return NULL to indicate success, and the
invoking statement executes. Otherwise, an error is indicated by setting an error or returning a non-NULL string. If a non-NULL
string is returned, it is included in the error to the user as the reason for failure.

The password verification function takes two parameters: user-name VARCHAR(128) and new-pwd VARCHAR(255). It returns a value of type VARCHAR(255). It is recommended that you 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 reuse, 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: samples-dir\SQLAnywhere\SQL\verify_password.sql. (For information about samples-dir, see Samples directory.)

-- only DBA should have permissions on this table
CREATE TABLE 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 the password conforms to password rules
CREATE 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 DEFAULT 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. In particular,
-- Interactive SQL and Sybase Central call the post_login_procedure.
ALTER LOGIN POLICY DEFAULT password_grace_time = 30;
-- Five consecutive failed login attempts will result in a non-DBA
-- user ID being locked.
ALTER LOGIN POLICY DEFAULT max_failed_login_attempts = 5;