Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 
Downloads > Code Samples & Utilities > Verify Password Function Sample Code  
RSS Feed
 
 
 

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:
  6. 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

  7. 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.
     
  8. Execute the following script:
     
    grant connect to demo identified by deM0
    go

     
  9. 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.
     

     
  10. Click OK to dismiss the error dialog.
     
  11. 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

     
  12. 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.
     
  13. 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.
     
  14. Shut down Interactive SQL.
     
  15. 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"
     
  16. Interactive SQL stats and successfully connects the user demo to the database.
     
  17. 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.


 

SQL Anywhere

The industry-leading mobile and embedded database that provides data management and synchronization technologies for applications outside the data center.

 
 
 

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics