Monday, March 12, 2012

Profiles and Password Verify Function - Oracle 11g

Verify Function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on.

In Oracle Database 11g, verify_fnction_11g function could be found on password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin.

At the end of the script following lines are available.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

By executing this script utlpwdmg.sql, it will attach the function to the profile DEFAULT, which is the default profile for all users.

Following query can be used to check the profile of the all users.

SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT'

Following query can be used to check the users who have the DEFAULT profile assigned.

SELECT USERNAME, PROFILE FROM DBA_USERS


 -----------------------------------------------------------------------------------------------------------------

Parameter Default Setting Description
SEC_CASE_SENSITIVE_LOGON TRUE Controls case sensitivity in passwords. TRUE enables case sensitivity; FALSE disables it.
SEC_MAX_FAILED_LOGIN_ATTEMPTS No default setting Sets the maximum number of times a user is allowed to fail when connecting to an Oracle Call Interface (OCI) application.
FAILED_LOGIN_ATTEMPTS 10 Sets the maximum times a user login is allowed to fail before locking the account.
Note: You also can set limits on the number of times an unauthorized user (possibly an intruder) attempts to log in to Oracle Call Interface applications by using the SEC_MAX_FAILED_LOGIN_ATTEMPTS initialization parameter.
PASSWORD_GRACE_TIME 7 Sets the number of days that a user has to change his or her password before it expires.
PASSWORD_LIFE_TIME 180 Sets the number of days the user can use his or her current password.
PASSWORD_LOCK_TIME 1 Sets the number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_REUSE_MAX UNLIMITED Sets the number of password changes required before the current password can be reused.
PASSWORD_REUSE_TIME UNLIMITED Sets the number of days before which a password cannot be reused.

4 comments:

  1. good...very helpful

    ReplyDelete
  2. precise and good..
    But if i want to drop the password verify function from default profile?

    ReplyDelete
  3. ALTER PROFILE DEFAULT LIMIT
    PASSWORD_VERIFY_FUNCTION NULL;

    ReplyDelete
  4. Is the password verify function ONLY applied to the default profile? Or can it be applied to other profiles as well?

    ReplyDelete