Sample code for password verification function [CR 440216]

Added for Sybase IQ 12.7 ESD #1, this example defines a number of procedures and functions which together implement advanced password rules, including requiring certain types of characters in the password, disallowing password reuse, and expiring passwords. These procedures and functions are called by the server using the VERIFY_PASSWORD_FUNCTION option and the LOGIN_PROCEDURE option when a User ID is created or a password is changed, and when a user connects. The application can call the procedure specified by the POST_LOGIN_PROCEDURE option to report that the password should be changed before it expires.

-- only DBA should have permissions on this table

CREATE TABLE DBA.t_pwd_history(
pk INT DEFAULT AUTOINCREMENT PRIMARY KEY,
change_date TIMESTAMP DEFAULT CURRENT TIMESTAMP,
                 -- when pwd set
grace_date DATE, -- a day after password expires to
                 -- allow user to log in
user_name CHAR(128), -- the user whose password is set
pwd_hash CHAR(32) ); -- hash of password value to detect
                     -- duplicate passwords

-- called on every GRANT CONNECT TO ... IDENTIFIED BY...
-- statement to verify that the password conforms to
-- the 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’;

-- called on every connection to check for password
-- expiry

CREATE PROCEDURE DBA.p_login_check()
BEGIN
DECLARE uid CHAR(128);
DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE ’28000’;
DECLARE last_pwd_change DATE;
DECLARE grace_date DATE;
DECLARE is_dba CHAR;
DECLARE msg_str CHAR(255);
SET uid = connection_property( ’Userid’ );
IF ( EXISTS( SELECT * FROM t_pwd_history
WHERE user_name = uid
) ) THEN
SELECT FIRST t.change_date, t.grace_date
INTO last_pwd_change, grace_date
FROM t_pwd_history t WHERE t.user_name = uid
ORDER BY t.change_date DESC;
END IF;

IF last_pwd_change IS NULL THEN
-- no password change in t_pwd_history, so create one
-- now
INSERT INTO t_pwd_history( user_name, pwd_hash )
VALUES( uid, ’unknown’ );
COMMIT WORK;
ELSE
IF EXISTS( SELECT * FROM SYS.SYSUSERAUTHORITY a,
SYS.SYSUSER u
WHERE u.user_name = uid AND u.user_id =
a.user_id AND
a.auth = ’DBA’ ) THEN
SET is_dba = ’Y’;
ELSE
SET is_dba = ’N’;
END IF;

-- remove any locks on t_pwd_history and
-- SYSUSERAUTHORITY
ROLLBACK WORK;

-- check if last password change was over five
-- months ago
IF CURRENT DATE > dateadd( month, 5, last_pwd_change )
THEN
-- never expire DBA accounts so that the database
-- does not get locked out by all users
IF CURRENT DATE < dateadd( month, 6, last_pwd_change
) OR
is_dba = ’Y’ OR
( grace_date IS NOT NULL AND grace_date = CURRENT
DATE ) THEN
SET msg_str = ’The password for user ’ || uid ||
’ expires on ’ ||
CAST( dateadd( month, 6, last_pwd_change )
AS DATE ) || ’. Please change it before it expires.’;
MESSAGE msg_str;

-- The post_login_procedure option is set to
-- p_post_login_check, which will cause a dialog to be
-- displayed notifying the user their password will
-- expire soon. dbisql and dbisqlc will display this
-- dialog, and user applications can call the
-- post_login_procedure and display this dialog.
-- May want to use xp_send_mail to notify user and/or
-- administrator.

ELSEIF grace_date IS NULL THEN

-- Allow one grace login day. The first login on
-- the grace day fails to ensure the user knows
-- their password has expired.

UPDATE t_pwd_history t SET t.grace_date = CURRENT DATE
WHERE t.grace_date IS NULL AND t.user_name = uid;
COMMIT WORK;
SET msg_str = ’The password for user ’ || uid || ’
has expired, but future logins will ’ ||
’be allowed today only so that the password ’ ||
’can be changed.’;
MESSAGE msg_str;
RAISERROR 28000 msg_str;
RETURN;
ELSE
SET msg_str = ’The password for user ’ || uid ||
’ has expired and must be reset by your DBA.’;
MESSAGE msg_str;
-- may want to use xp_send_mail to notify administrator
RAISERROR 28000 msg_str;
RETURN;
END IF;
END IF;
END IF;

CALL sp_login_environment;
END;

GRANT EXECUTE ON DBA.p_login_check TO PUBLIC;
SET OPTION PUBLIC.login_procedure =
’DBA.p_login_check’;

-- called by dbisql, dbisqlc and some user applications
-- on every successful connection to check for warnings
-- which should be displayed

CREATE PROCEDURE DBA.p_post_login_check()
RESULT( warning_text VARCHAR(255), warning_action INT )
BEGIN
DECLARE uid CHAR(128);
DECLARE last_pwd_change DATE;
DECLARE warning_text CHAR(255);
DECLARE warning_action INT;
SET uid = connection_property( ’Userid’ );
SELECT FIRST t.change_date
INTO last_pwd_change
FROM t_pwd_history t WHERE t.user_name = uid
ORDER BY t.change_date DESC;
IF CURRENT DATE > dateadd( month, 5, last_pwd_change )
THEN
SET warning_text = ’Your password expires on ’ ||
CAST( dateadd( month, 6, last_pwd_change )
AS DATE ) || ’. Please change it before it expires.’;
SET warning_action = 1;
ELSE
-- There is no warning
SET warning_text = NULL;
SET warning_action = 0;
END IF;
-- return the warning (if any) through this result set
SELECT warning_text, warning_action;
END;


GRANT EXECUTE ON DBA.p_post_login_check TO PUBLIC;
SET OPTION PUBLIC.post_login_procedure =
’DBA.p_post_login_check’;