Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - Database Administration » Keeping Your Data Secure » Controlling database access

Controlling database access Next Page

Increasing password security

Passwords are an important part of any database security system. To be secure, passwords must be difficult to guess, and they must not be easily accessible on users' hard drives or other locations. SQL Anywhere passwords are always case sensitive. You can specify a function used for password authentication with the verify_password_function option. See verify_password_function option [database].

Implement minimum password lengths

By default, passwords can be any length. For greater security, you can enforce a minimum length requirement on all new passwords. You do this by setting the min_password_length database option to a value greater than zero. The following statement enforces passwords to be at least 8 bytes long.

SET OPTION PUBLIC.min_password_length = 8;

See min_password_length option [database].

Implement password expiration

By default, database passwords never expire. You can use the post_login_procedure option to implement such functionality as warning users that their password is going to expire or changing passwords. See post_login_procedure [database].

Do not include passwords in ODBC data sources

Passwords are the key to accessing databases. They should not be easily available to unauthorized people in a security-conscious environment.

When you create an ODBC data source or a Sybase Central connection profile, you can optionally include a password. Avoid including passwords to ensure that they are not viewed by unauthorized users.

See Creating an ODBC data source.

Encrypt configuration files containing passwords

When you create a configuration file, you can optionally include password information. To protect your passwords, consider hiding the contents of configuration files with simple encryption, using the File Hiding (dbfhide) utility. See File Hiding utility (dbfhide).

Use a password verification function

You can use the verify_password_function option to specify a function that implements password rules. See verify_password_function option [database].

The following example defines a number of procedures and functions. Together they implement advanced password rules that include requiring certain types of characters in the password, disallowing password reuse, and expiring passwords. These procedures and functions are called by the database server with the verify_password_function and login_procedure options 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.

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,
        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 the password conforms to password rules
CREATE FUNCTION DBA.f_verify_pwd( uid     VARCHAR(128),
                                  new_pwd VARCHAR(255) )
    -- 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)
                    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' ) );


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()
    DECLARE uid                 CHAR(128);
    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;
                   WHERE u.user_name = uid AND u.user_id = a.user_id AND
                         a.auth = 'DBA' ) THEN
            SET is_dba = 'Y';
            SET is_dba = 'N';
        END IF;
        -- remove any locks on t_pwd_history and SYSUSERAUTHORITY
        -- 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;
                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;
            END IF;
        END IF;
    END IF;

    CALL sp_login_environment;

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 )
    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;
        -- 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;

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