Profile management : #01

Password management

Password management can be easily controlled by a DBA through the use of profiles.

Enabling Password Management

Password management is enabled by creating a profile and assigning the profile to system users when their account is created or by altering system user profile assignments.

Password limits set in this fashion are always enforced. When password management is in use, an existing user account can be locked or unlocked by the ALTER USER command.

Password Account Locking: 

This option automatically locks a system user account if the user fails to execute proper login account name/password entries after a specified number of login attempts.

The FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME parameter are specified as part of a profile.

The FAILED_LOGIN_ATTEMPTS is specified as an integer. 

The PASSWORD_LOCK_TIME is specified as days.

The database account can be explicitly locked with the ALTER USER command. When this happens, the account is not automatically unlocked.

Password Expiration/Aging: 

Specifies the lifetime of a password – after the specified period, the password must be changed.

The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters are specified as part of a profile.

PASSWORD_LIFE_TIME specifies the maximum life of a password.

If the PASSWORD_GRACE_TIME is exceeded, the account automatically locks.

Both of these parameters are specified in days.

Password History:

This option ensures that a password is not reused within a specified period of time or number of password changes.

If either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX are set to a value other than DEFAULT or UNLIMITED, the other parameter must be set to UNLIMITED.

PASSWORD_REUSE_TIME is specified in days.

PASSWORD_REUSE_MAX is an integer value specifying the number of password changes required before a password can be reused.

If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.

If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED

Password Complexity Verification:

This option ensures that a password is complex – this helps provide protection against system intruders who attempt to guess a password.

This is implemented by use of a password verification function. A DBA can write such a function or can use the default function named VERIFY_FUNCTION.

The function that is used for password complexity verification is specified with the profile parameter, PASSWORD_VERIFY_FUNCTION.

If NULL is specified (the default), no password verification is performed.

When a DBA connected as the user SYS executes the utlpwdmg.sql script (located at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql) , the Oracle Server creates the VERIFY_FUNCTION .

[oracle@localhost admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 11 07:19:45 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.

Profile altered.

Function created.

Grant succeeded.

SQL>

Creating a Profile with Password Protection:

SQL> CREATE PROFILE developer LIMIT

PASSWORD_REUSE_TIME 1

PASSWORD_LOCK_TIME 2

PASSWORD_REUSE_MAX 3

FAILED_LOGIN_ATTEMPTS 2

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION;  

Profile created.

SQL>SELECT * FROM DBA_PROFILES WHERE PROFILE ='DEVELOPER';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

DEVELOPER                      COMPOSITE_LIMIT                  KERNEL   DEFAULT

DEVELOPER                      SESSIONS_PER_USER                KERNEL   DEFAULT

DEVELOPER                      CPU_PER_SESSION                  KERNEL   DEFAULT

DEVELOPER                      CPU_PER_CALL                     KERNEL   DEFAULT

DEVELOPER                      LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

DEVELOPER                      LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

DEVELOPER                      IDLE_TIME                        KERNEL   DEFAULT

DEVELOPER                      CONNECT_TIME                     KERNEL   DEFAULT

DEVELOPER                      PRIVATE_SGA                      KERNEL   DEFAULT

DEVELOPER                      FAILED_LOGIN_ATTEMPTS            PASSWORD 2

DEVELOPER                      PASSWORD_LIFE_TIME               PASSWORD DEFAULT

DEVELOPER                      PASSWORD_REUSE_TIME              PASSWORD 1

DEVELOPER                      PASSWORD_REUSE_MAX               PASSWORD 3

DEVELOPER                      PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION

DEVELOPER                      PASSWORD_LOCK_TIME               PASSWORD 2

DEVELOPER                      PASSWORD_GRACE_TIME              PASSWORD DEFAULT

SQL> create user tes identified by te;

create user tes identified by te

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8

SQL>

Using the Data Dictionary:

Information about password and resource limits can be obtained by querying the following views:

DBA_USERS

DBA_PROFILES