Verify Password Function Sample

This sample illustrates how SQL Anywhere 10 further enhances security by accommodating password rules using the new database option verify_password_function. Database options are used for many purposes, including compatibility, error handling, concurrency, and transactions.

For more information about the new database options in SQL Anywhere 10, refer to the online documentation: What's New in SQL Anywhere | What's New in Version 10.0.0 | SQL Anywhere New features | Database options.

Requirements

• SQL Anywhere 10.0

You can run this sample under Windows or Linux.

Walkthrough

1. Shut down any SQL Anywhere databases that are running on your computer.
    
2. Open a command prompt and create a temporary directory. Change to this directory.
    
3. Create a new empty database and start it by running the following commands:
    
   dbinit pwd.db
   dbeng10 pwd.db
    
4. Start Interactive SQL and connect to the sample database by running the following command:
    
   dbisql -c "uid=dba;pwd=sql;eng=pwd"
    
5. Copy and paste the following script into Interactive SQL and execute it (press F5) to create a new user name called demo and define the password rules function:

grant connect to demo identified by demo
go
call sa_make_object('function','fn_verify_pwd')
go
alter function fn_verify_pwd(uid char(128),pwd char(128))
returns varchar(255)
begin

declare i int;
declare code int;
declare lowerfound int;
declare upperfound int;
declare numfound int;
set upperfound=0;
set lowerfound=0;
set numfound=0;
set i=1;
if length(pwd) < 8 then
message 'Password not changed';
return( 'Password is not long enough.' );
end if;
if pwd=uid then
message 'Password not changed';
return( 'Password must be different from user name' );
end if;

while i <= length(pwd) loop
set code= ascii(substring(pwd,i,1));
if code between 65 and 90 then
set upperfound=1;
end if;
if code between 97 and 123 then
set lowerfound=1;
message 'lower';
end if;
if code between 48 and 58 then
set numfound=1;
end if;
set i=i+1;
end loop;
message (lowerfound+upperfound+numfound);
if ((lowerfound+upperfound+numfound) < 3) then
message 'Password not changed';
return( 'Password rules have not been satisfied.' );
end if;
return(NULL);
end
go
alter function fn_verify_pwd set hidden
go
grant execute on fn_verify_pwd to PUBLIC
go
set option PUBLIC.verify_password_function = 'DBA.fn_verify_pwd'
go

6. Examine the code for the function fn_verify_password. This function is called each time a new user is created to enforce the password rules. After defining the function, it is passed to the verify_password_function database option.
    
   These rules require that a given password be at least 8 characters in length, with at least one uppercase and one lowercase letter, and at least one number. In addition, the password cannot be the same as the user name.
    
7. Execute the following script:
    
   grant connect to demo identified by deM0
   go
    
8. This script attempts to change the password, but you see an error message indicating an invalid password because it does not satisfy the conditions given in the password function.
    
    
9. Click OK to dismiss the error dialog.
    
10. You can attempt to change the password a few more times by executing the following script:
     
    grant connect to demo identified by demoDEMO
    go
    grant connect to demo identified by demo01234
    go
    grant connect to demo identified by DEMO01234
    go
     
11. These passwords also do not satisfy the rules, and so you will receive more error messages. Click Continue after each error dialog. Note that the password was not changed for the user demo despite the four attempts.
     
12. Now execute this script:
     
    grant connect to demo identified by deM000000
    go
     
    This script successfully changes the password to deM000000 as this conforms to the password rules.
     
13. Shut down Interactive SQL.
     
14. To verify that the password was changed, restart Interactive SQL and connect to the database with user demo and the new password:
     
    dbisql -c "uid=demo;pwd=deM000000;eng=pwd"
     
15. Interactive SQL stats and successfully connects the user demo to the database.
     
16. This concludes the sample. Close Interactive SQL and shut down the database server by running this command:
     
    dbstop -c "uid=demo;pwd=deM000000;eng=pwd"

Clean-up

Remove the temporary folder you created in step 2 and all its contents.