Profiles and password management
| This is a work in progress. Help us to improve this page or provide feedback on the discussion page.
Expect this article to change frequently.
What is Profile
A profile is a database object - a named set of resource limits to:
- Restrict database usage by a system user – profiles restrict users from performing operations that exceed reasonable resource utilization. Examples of resources that need to be managed:
- Disk storage space.
- I/O bandwidth to run queries.
- CPU power.
- Connect time.
- Cpu time
- Enforce password practices – how user passwords are created, reused, and validated.
- Profiles are assigned to users as part of the CREATE USER or ALTER USER commands.
- User accounts can have only a single profile.
- A default profile can be created – a default already exists within Oracle named DEFAULT – it is applied to any user not assigned another profile.
- Assigning a new profile to a user account supersedes any earlier profile.
- Profiles cannot be assigned to roles or other profiles.
Here are some system privileges for PROFILE.
- alter profile
- create profile
- drop profile
Benefits of Profile
You can enforce a limit on resource utilization using resource limit parameters. Also you can maintain database security by using password management feature.
|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.|
Profiles only take effect when resource limits are "turned on" for the database as a whole.
• Specify the RESOURCE_LIMIT initialization parameter.
RESOURCE_LIMIT = TRUE
Let check the parameter value.
SQL> show parameter resource_limit NAME TYPE VALUE ------------------------------------ ----------- --------- resource_limit boolean FALSE
Its mean resource limit is off,we ist have to enable it.
• Use the ALTER SYSTEM statement to turn on resource limits.
SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; System altered. SQL> show parameter resource_limit NAME TYPE VALUE ------------------------------------ ----------- --------- resource_limit boolean TRUE
• Resource limit specifications pertaining to passwords are always in effect.
Now I'm going to create a profile with the name my_profile.
SQL> CREATE PROFILE my_profile LIMIT 2 SESSIONS_PER_USER 2 3 IDLE_TIME 5 4 CONNECT_TIME 10; Profile created.
In the above example i created simple profile which will handle
SESSIONS_PER_USER <<<I'm able to open 2 sessions concurrent IDLE_TIME <<<My session will be terminate automatically after the time specified for this parameter. CONNECT_TIME <<<It will keep me online until the value of this parameter.
Both parameters take values in min.
Now I'm creating a test user to check the functionality of this profile.
SQL> create user Michel identified by michel 2 default tablespace users 3 temporary tablespace temp; User created.
SQL> alter user Michel profile my_profile; User altered.
With the above statement i assigned the profile my_profile to user Michel.
Let see how our profile will work.
I already opened 2 sessions with the user name Michel but when i tried for third session it throws this error.
sqlplus Michel SQL*Plus: Release 126.96.36.199.0 - Production on Mon Nov 26 15:57:23 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Enter password: ERROR: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
You noticed when i tried to open third session it gave me error.
Lets go to 2nd step IDLE_TIME.Here we go again
SQL> select * from tab; select * from tab * ERROR at line 1: ORA-02396: exceeded maximum idle time, please connect again
Because i was idle more than 5 min so thats why Oracle server kill mine session.
We can check the resource parameter of our profile by querying DBA_PROFILES.
SQL> select * from dba_profiles 2 where profile='MY_PROFILE'; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ------- MY_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT MY_PROFILE SESSIONS_PER_USER KERNEL 2 MY_PROFILE IDLE_TIME KERNEL 5 MY_PROFILE CONNECT_TIME KERNEL 10 . . .
Profile can be assign in two ways either during USER creation or by using ALTER statement.
SQL> create user orafaq identified by pass profile my_profile; User created.
We can check it by using this query.
SQL> select username,profile from dba_users where username='ORAFAQ';
USERNAME PROFILE ------------------------------ -------------- ORAFAQ MY_PROFILE
SQL> drop user orafaq cascade; User dropped. SQL> create user orafaq identified by pass; User created. SQL> alter user orafaq profile my_profile; User altered.
Profiles can be altered with the ALTER PROFILE command. • A DBA must have the ALTER PROFILE system privilege to use this command. • When a profile limit is adjusted, the new setting overrides the previous setting for the limit, but these changes do not affect current sessions in process. See the example below
SQL> ALTER PROFILE accountant LIMIT 2 CPU_PER_CALL default 3 LOGICAL_READS_PER_SESSION 20000 4 SESSIONS_PER_USER 1;
Profiles no longer required can be dropped with the DROP PROFILE command.
- The DEFAULT profile cannot be dropped.
- 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.
- When a profile is dropped, any user account with that profile is reassigned the DEFAULT profile. See example below:
SQL> DROP PROFILE accountant; ERROR at line 1: ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE SQL> DROP PROFILE accountant CASCADE;
|FAILED_LOGIN_ATTEMPTS||Maximum times the user is allowed in fail login before locking the user account|
|PASSWORD_LIFE_TIME||Number of days the password is valid before expiry|
|PASSWORD_REUSE_TIME||Number of day after the user can use the already used password|
|PASSWORD_REUSE_MAX||Number of times the user can use the already used password|
|PASSWORD_LOCK_TIME||Number of days the user account remains locked after failed login|
|PASSWORD_GRACE_TIME||Number of grace days for user to change password|
|PASSWORD_VERIFY_FUNCTION||PL/SQL that can be used for password verification|
|SEC_CASE_SENSITIVE_LOGON||To control the case sensitivity in passwords|
Enabling Password Management
Restrictions on password parameters
How profiles are stored
Profiles can be viewed via the DBA_PROFILE views (available only with DBA role):
SQL> describe DBA_PROFILES Name Null? Type ------------- -------- ------------- PROFILE NOT NULL VARCHAR2(30) RESOURCE_NAME NOT NULL VARCHAR2(32) RESOURCE_TYPE VARCHAR2(8) LIMIT VARCHAR2(40)
There are only two RESOURCE TYPEs, "KERNEL" and "PASSWORD"; and 16 RESOURCE NAMEs which can be given a limit; the limit in the DEFAULT profile is written in the 3rd column (as of 11gR2):
RESOURCE_NAME RESOURCE_TYPE LIMIT --------------------------- ------------- -------------- COMPOSITE_LIMIT KERNEL UNLIMITED CONNECT_TIME KERNEL UNLIMITED CPU_PER_CALL KERNEL UNLIMITED CPU_PER_SESSION KERNEL UNLIMITED IDLE_TIME KERNEL UNLIMITED LOGICAL_READS_PER_CALL KERNEL UNLIMITED LOGICAL_READS_PER_SESSION KERNEL UNLIMITED PRIVATE_SGA KERNEL UNLIMITED SESSIONS_PER_USER KERNEL UNLIMITED FAILED_LOGIN_ATTEMPTS PASSWORD 10 PASSWORD_GRACE_TIME PASSWORD 7 PASSWORD_LIFE_TIME PASSWORD UNLIMITED PASSWORD_LOCK_TIME PASSWORD 1 PASSWORD_REUSE_MAX PASSWORD UNLIMITED PASSWORD_REUSE_TIME PASSWORD UNLIMITED PASSWORD_VERIFY_FUNCTION PASSWORD NULL