Profiles

• Change
• Create
• Display
• Drop
• Hardening Profiles
• Password Reset Profile

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.

 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