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.
Specify the number of concurrent sessions to which you want to limit the user.
Specify the CPU time limit for a session, expressed in hundredth of seconds.
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
Specify the total elapsed time limit for a session, expressed in minutes.
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.
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.
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 * 10
PASSWORD_LIFE_TIME :Number of days the password is valid before expiry * 108 days
PASSWORD_REUSE_TIME :Number of day after the user can use the already used password * UNLIMITED
PASSWORD_REUSE_MAX :Number of times the user can use the already used password * UNLIMITED
PASSWORD_LOCK_TIME :Number of days the user account remains locked after failed login * 1 day
PASSWORD_GRACE_TIME :Number of grace days for user to change password * 7 days
PASSWORD_VERIFY_FUNCTION :PL/SQL that can be used for password verification * NO DEFAULT SETTING
SEC_CASE_SENSITIVE_LOGON :To control the case sensitivity in passwords * TRUE
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