Profiles and password management
From Oracle FAQ
| 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. |
|
Contents |
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.
- 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
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.
Creating Profile
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
SESSIONS_PER_USER 2
IDLE_TIME 5
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.
NOTE:
Both parameters take values in min.
Now I'm creating a test user to check the functionality of this profile.
SQL> create user dreamzZ identified by dreamzz
default tablespace users
temporary tablespace temp;
User created.
SQL> alter user dreamzz profile my_profile; User altered.
With the above statement i assigned the profile my_profile to user dreamzz.
Let see how our profile will work.
I already opened 2 sessions with the user name dreamzz but when i tried for third session it thorwed this error.
sqlplus dreamzz SQL*Plus: Release 11.1.0.6.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
where profile='MY_PROFILE';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ------
---------------------------------
MY_PROFILE COMPOSITE_LIMIT KERNEL DEFAUL
MY_PROFILE SESSIONS_PER_USER KERNEL 2
MY_PROFILE IDLE_TIME KERNEL 5
MY_PROFILE CONNECT_TIME KERNEL 10
.
.
.
Assigning Profile
Profile can be assign in two ways either during USER creation or by using ALTER statement.
Case 1:
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
CASE 2:
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.
