oracledba.help
Security

Profiles

Overview

A Profile is a named set of resource limits and password parameters that restrict database usage and instance resources for a user.

  • FAILED_LOGIN_ATTEMPTS
    If a user attempts to login more than the specified number of times the account will be locked. Default is 10 attempts.
  • PASSWORD_GRACE_TIME
    Number of grace days for user to change password. Default is 7 days.
  • PASSWORD_LIFE_TIME
    Number of days the same password can be used unless a grace period is specified. Default is 180 days.
  • PASSWORD_LOCK_TIME
    Number of days an account will remain locked after failed login attempts has been triggered. Default is 1 day.
  • PASSWORD_REUSE_MAX
    Number of times a password must be changed before a previous password can be used again. Default is unlimited.
  • PASSWORD_REUSE_TIME
    Number of days that must pass before the same password can be used again. Default is unlimited.
  • PASSWORD_VERIFY_FUNCTION
    Allows you to define PL/SQL that can be used for password verification. Setting to VERIFY_FUNCTION_11G adds complexity
    to user passwords. To enable this run %ORACLE_HOME%\rdbms\admin\utlpwdmg.sql.
    Warning: Running this script may EXPIRE accounts set to the DEFAULT profile.

Warning

Running utlpwdmg.sql changes the DEFAULT profile. Existing accounts may be set to expired! You may need to run the following and reset the password of accounts to their current password to get status back to a non-expired state and avoid interrupted existing operations.

 ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION NULL;
 ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;
 ALTER PROFILE default LIMIT PASSWORD_GRACE_TIME 365;

 ALTER USER scott IDENTIFIED BY "OriginalPassword";

Change

ALTER PROFILE <profile_name> LIMIT <parameter_name> <value>;
ALTER PROFILE user_accounts LIMIT SESSIONS_PER_USER 10;
ALTER PROFILE user_accounts LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE default LIMIT PASSWORD_LOCK_TIME UNLIMITED;

Create

CREATE PROFILE user_accounts
   LIMIT 
   FAILED_LOGIN_ATTEMPTS  3
   PASSWORD_GRACE_TIME    5
   PASSWORD_LIFE_TIME    90
   PASSWORD_LOCK_TIME     1
   PASSWORD_REUSE_MAX    20
   PASSWORD_REUSE_TIME  365
   SESSIONS_PER_USER      5
;

ALTER USER scott PROFILE user_accounts;

Drop

DROP PROFILE <ProfileName>;

DROP PROFILE svc_accounts;

Display

COL profile        FORMAT a20
COL resource_name  FORMAT a25
COL limit          FORMAT a10

-- Profile Values

SELECT * 
FROM dba_profiles 
WHERE profile       = 'DEFAULT'
AND   resource_type = 'PASSWORD';

-- User in Profiles

SELECT username, profile
FROM dba_users 
ORDER BY username;

Hardening Profiles

Generally one for system\services\jobs (DEFAULT) and one for user accounts (USER_ACCOUNTS ).

ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS    3;
ALTER PROFILE default LIMIT PASSWORD_GRACE_TIME      365;
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME       UNLIMITED;
ALTER PROFILE default LIMIT PASSWORD_LOCK_TIME       1;
ALTER PROFILE default LIMIT PASSWORD_REUSE_MAX       20;
ALTER PROFILE default LIMIT PASSWORD_REUSE_TIME      UNLIMITED;
ALTER PROFILE default LIMIT SESSIONS_PER_USER        UNLIMITED;
ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

CREATE PROFILE USER_ACCOUNTS 
   LIMIT 
   FAILED_LOGIN_ATTEMPTS 3
   PASSWORD_GRACE_TIME   5
   PASSWORD_LIFE_TIME    90
   PASSWORD_LOCK_TIME    1
   PASSWORD_REUSE_MAX    20
   PASSWORD_REUSE_TIME   365
   SESSIONS_PER_USER     5
;
ALTER PROFILE user_accounts LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

Password Reset Method

This method can be used to reset a password if it erroneously entered a grace period. In this scenario USER_ACCOUNTS was the original profile.

1. Create a profile to hold account temporarily.
   CREATE PROFILE reset
   LIMIT 
   FAILED_LOGIN_ATTEMPTS UNLIMITED
   PASSWORD_GRACE_TIME   UNLIMITED
   PASSWORD_LIFE_TIME    UNLIMITED
   PASSWORD_LOCK_TIME    1
   PASSWORD_REUSE_MAX    UNLIMITED
   PASSWORD_REUSE_TIME   UNLIMITED
   SESSIONS_PER_USER     UNLIMITED
   PASSWORD_VERIFY_FUNCTION NULL
   ;

2. Set account to new temp profile.
   ALTER USER &User PROFILE reset;

3. Reset password.
   ALTER USER &User IDENTIFIED BY "&NewPW";

4. Reset back to original profile.
   ALTER USER &User PROFILE USER_ACCOUNTS;

<- Security