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