Profiles and password management

From Oracle FAQ
Jump to: navigation, search
Road Works 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.

Road Works


Contents

[edit] 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

[edit] Benefits of Profile

You can enforce a limit on resource utilization using resource limit parameters. Also you can maintain database secutiry by using password management feature.

[edit] 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.

[edit] 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 Michel identified by michel
    default tablespace users
    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 thorwed 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
     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
.
.
.

[edit] 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.

[edit] 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 CPU_PER_CALL default LOGICAL_READS_PER_SESSION 20000 SESSIONS_PER_USER 1;

[edit] 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;

[edit] Password Management

[edit] Password Parameters

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 fro password verification * NO DEFAULT SETTING

SEC_CASE_SENSITIVE_LOGON  :To control the case sensitivity in passwords * TRUE

[edit] Enabling Password Management

[edit] Restrictions on password parameters

[edit] 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

[edit] Examples