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.

10 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
    Replies
    1. one or many. I have 2 profiles, and DEFAULT is using my updated verify function and my SAPUPROF profile points to DEFAULT (which will use defaults setting). But I could create many different profiles and assign each its own verify function.

      Delete
    2. we can assign the Verification function to any profile by following code



      GRANT EXECUTE ON verify_function TO PUBLIC;

      Rem *************************************************************************
      Rem END Password Verification Functions
      Rem *************************************************************************

      Rem *************************************************************************
      Rem BEGIN Password Management Parameters
      Rem *************************************************************************

      -- This script alters the default parameters for Password Management
      -- This means that all the users on the system have Password Management
      -- enabled and set to the following values unless another profile is
      -- created with parameter values set to different value or UNLIMITED
      -- is created and assigned to the user.

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

      /**
      The below set of password profile parameters would take into consideration
      recommendations from Center for Internet Security[CIS Oracle 11g].

      ALTER PROFILE DEFAULT LIMIT
      PASSWORD_LIFE_TIME 90
      PASSWORD_GRACE_TIME 3
      PASSWORD_REUSE_TIME 365
      PASSWORD_REUSE_MAX 20
      FAILED_LOGIN_ATTEMPTS 3
      PASSWORD_LOCK_TIME 1
      PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
      */

      /**
      The below set of password profile parameters would take into
      consideration recommendations from Department of Defense Database
      Security Technical Implementation Guide[STIG v8R1].

      ALTER PROFILE DEFAULT LIMIT
      PASSWORD_LIFE_TIME 60
      PASSWORD_REUSE_TIME 365
      PASSWORD_REUSE_MAX 5
      FAILED_LOGIN_ATTEMPTS 3
      PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;
      */

      Rem *************************************************************************
      Rem END Password Management Parameters
      Rem *************************************************************************

      Delete
    3. Thanks for your input on the content

      Delete
    4. Thanks for your input on the content

      Delete
  5. Hello,

    Is the PASSWORD_VERIFY_FUNCTION being executed when the password of database account is being changed? Or when the PASSWORD_VERIFY_FUNCTION is being enabled, it will check against all existing database account and locked the accounts which cannot pass the PASSWORD_VERIFY_FUNCTION?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete