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.

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
  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.

NOTE:

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 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
 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
.
.
.

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.

Altering Profile

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;

Dropping Profile

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;

Password Parameters

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

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