Adaptive Server allows a system security officer to write user-defined stored procedures that enable custom password checks.
For example, to implement password history checks, create a new user table to store password histories:
create table pwdhistory ( name varchar(30)not null, -- Login name. password varbinary(30)not null, -- old password. pwdate datetime not null, -- datetime changed. changedby varchar(30)not null -- Who changed. ) go
This user-defined stored procedure (sp_extrapwdchecks) can be called when specifying a new password to save it in an encrypted form in the pwdhistory table:
create proc sp_extrapwdchecks ( @caller_password varchar(30), -- the current password of caller @new_password varchar(30), -- the new password of the target acct @loginame varchar(30) -- user to change password on ) as begin declare @current_time datetime, @encrypted_pwd varbinary(30), @salt varchar(8), @changedby varchar(30), @cutoffdate datetime select @changedby = suser_name() select @salt = null -- Change this line according to your installation. -- This keeps history of 12 months only. select @current_time = getdate(), @cutoffdate = dateadd(month, -12, getdate()) delete master..pwdhistory where name = @loginame and pwdate < @cutoffdate select @salt = substring(password, 1, 8) from master..pwdhistory where pwdate = (select max(pwdate) from master..pwdhistory where name=@loginame) and name=@loginame if @salt is null begin select @salt = substring(hash(password_random(), 'sha1'), 1, 8) end select @encrypted_pwd = @salt + hash(@salt + @new_password, 'sha1') if not exists ( select 1 from master..pwdhistory where name = @loginame and password = @encrypted_pwd ) begin insert master..pwdhistory select @loginame, @encrypted_pwd, @current_time, @changedby return (0) end else begin raiserror 22001 --user defined error message end end go
Use sp_addmessage to add the user-defined
message 22001. A raiserror
22001
indicates a custom password-complexity check error.
The following user-defined stored procedure (sp_cleanpwdchecks) can be used to clean-up the password history using sp_extrapwdchecks.
create proc sp_cleanpwdchecks ( @loginame varchar(30) -- user to change password on ) as begin delete master..pwdhistory where name = @loginame end go
Once the two procedures above are defined and installed in the master database, they are called dynamically during the password complexity checks.