Profiles in user management

Profiles in  user  management  

what  is profile ?

- Profile is a set of limits on database resources

- Once we assign  the  user with in the profile then that  user cannot exceed the limits

- Before creating   profile, we must enable resource_limit parameter

Resource Limit

- Resource limits are  enforced in database profiles

- Profiles only take effect when resource limits are "turned on" for the database as a whole

Check  resource_limit  

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------

resource_limit                       boolean     FALSE

enable these parameter

SQL>Alter system set resource_limit=TRUE;

System altered.

check the parameter again

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------

resource_limit                       boolean     TRUE

 Resource Parameters

*SESSION_PER_USER - specify the no of concurrent session allow 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 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.  

Creating  Profile Resource Parameters

SQL> Create profile MY_TEST LIMIT

SESSIONS_PER_USER 2

       IDLE_TIME 5

       CONNECT_TIME 10;

in above I’m created one profile along with limits then i created a user  for profile

SQL> create user SAM identified by oracle;

Assign the user to profile

SQL>alter user SAM profile MY_TEST;

User altered.

right now, i will login as SAM user  and over limitation as we assigned  means  already  opened two  sessions  and i tired connect  third session it ll throw an error

sqlplus SAM

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

Let’s 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

here my session idle  time is more than 5 mins that why  oracle server kills mine session.

To view  profile limitations

SQL>select * from dba_profiles where profile='MY_TEST';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

MY_TEST                      COMPOSITE_LIMIT                  KERNEL   DEFAUL

MY_TEST                      SESSIONS_PER_USER                KERNEL   2

MY_TEST                      IDLE_TIME                        KERNEL   5

MY_TEST                      CONNECT_TIME                     KERNEL   10

 PASSWORD MANAGEMENT

*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 days 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  

Check the profile

only DBA role person to view this

SQL> describe DBA_PROFILES

Name          Null?    Type

PROFILE       NOT NULL VARCHAR2(30)

RESOURCE_NAME NOT NULL VARCHAR2(32)

RESOURCE_TYPE          VARCHAR2(8)

SQL> select  * from dba_profiles ;

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

 Alter  Profile

-We can alter  the profile once we created

SQL> alter profile MY_TEST LIMIT SESSIONS_PER_USER 1;

system altered.

View the profile  

SQL>select * from dba_profiles where profile='MY_TEST';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

MY_TEST                      COMPOSITE_LIMIT                  KERNEL   DEFAUL

MY_TEST                      SESSIONS_PER_USER                KERNEL   1

MY_TEST                      IDLE_TIME                        KERNEL   5

MY_TEST                      CONNECT_TIME                     KERNEL   10

  Drop Profile

- Drop profile using  "Drop Profile" command

- We  can drop "Default Profile" - If the user has been assigned to  profile, we can't  drop  the profile, so  we use the CASCADE it drops the profile and it ll change user into default profile

SQL> DROP PROFILE MY_TEST;

ERROR at line 1:

ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE

SQL> DROP PROFILE MY_TEST CASCADE;