Oracle Profiles and Password Management

A profile is a database object, a named set of resource limits to enforce a limit on resource utilization using resource limit parameters. Also you can maintain database security by using password management feature. A profile of any user or schema enables to Manage User Policies, Password Management, and many more security limits.

Resource Parameters

• sessions_per_user
Specify the number of concurrent sessions to which you want to limit the user.
• cpu_per_session
Specify the CPU time limit for a session, expressed in hundredth of seconds.
• cpu_per_call
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
• connect_time
Specify the total elapsed time limit for a session, expressed in minutes.
• idle_time
Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
•logical_reads_per_session
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
•logical_reads_per_call
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
•private_sga
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.
•composite_limit
Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

Password Parameters

• failed_login_attempts
This is the number of failed login attempts before locking the Oracle user account. The default is three failed attempts.
• password_grace_time – This is the grace period after the password_life_time limit is exceeded.
• password_life_time – This is how long an existing password is valid. The default here forces a password change every 60 days.
• password_lock_time – This specifies how long to lock the account after the failed login attempts is met. Most DBA’s set this value to UNLIMITED.
• password_reuse_max – This is the number of times that you may reuse a password and is intended to prevent repeating password cycles (north, south, east, west).
• password_reuse_time – This parameter specifies a time limit before a previous password can be re-entered. To allow unlimited use of previously used passwords, set password_reuse_time to UNLIMITED.
• password_verify_function – This allows you to specify the name of a custom password verification function.

Creating Profile

Profiles only take effect when resource limits are “turned on” for the database as a whole.

SQL> show parameter resource_limit;
SQL> show parameter resource_limit;

Now we are going to create a profile with the name VIVEK

SQL> CREATE PROFILE VIVEK LIMIT
  2  SESSIONS_PER_USER 5
  3  IDLE_TIME 10
  4  CONNECT_TIME 10;

Profile created.

We can check the resource parameter of our profile by querying Dynamic Performance View DBA_PROFILES.

SQL> select * from dba_profiles where profile='VIVEK';

PROFILE RESOURCE_NAME RESOURCE LIMIT
VIVEK COMPOSITE_LIMIT KERNEL DEFAULT
VIVEK SESSIONS_PER_USER KERNEL 5
VIVEK CPU_PER_SESSION KERNEL DEFAULT
VIVEK CPU_PER_CALL KERNEL DEFAULT
VIVEK LOGICAL_READS_PER_SESSION KERNEL DEFAULT
VIVEK LOGICAL_READS_PER_CALL KERNEL DEFAULT
VIVEK IDLE_TIME KERNEL 10
VIVEK CONNECT_TIME KERNEL 10
VIVEK PRIVATE_SGA KERNEL DEFAULT
VIVEK FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
VIVEK PASSWORD_LIFE_TIME PASSWORD DEFAULT

PROFILE RESOURCE_NAME RESOURCE LIMIT
VIVEK PASSWORD_REUSE_TIME PASSWORD DEFAULT
VIVEK PASSWORD_REUSE_MAX PASSWORD DEFAULT
VIVEK PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
VIVEK PASSWORD_LOCK_TIME PASSWORD DEFAULT
VIVEK PASSWORD_GRACE_TIME PASSWORD DEFAULT

16 rows selected.

Assigning Profile

Profile can be assign in two ways either during USER creation or by using ALTER statement.
Case 1:

SQL> CREATE USER VAG
  2  IDENTIFIED BY "vivek@123"
  3  PROFILE VIVEK;

User created.

Case 2:

SQL> ALTER USER VAG
  2  PROFILE VIVEK;
User altered.

Altering Profile

SQL> ALTER PROFILE VIVEK
  2  LIMIT CPU_PER_CALL default
  3  LOGICAL_READS_PER_SESSION 4000
  4  SESSIONS_PER_USER 2;
Profile altered.

Dropping Profile

Dropping the profile without using cascade option will gives an error because it is assigned to a User.
The CASCADE clause revokes the profile from any user account to which it was assigned – the CASCADE clause MUST BE USED if the profile has been assigned to any user account.

SQL> DROP PROFILE VIVEK ;
DROP PROFILE VIVEK
*
ERROR at line 1:
ORA-02382: profile VIVEK has users assigned, cannot drop without CASCADE
SQL> DROP PROFILE VIVEK CASCADE;
Profile dropped.

 

e