Let us we discuss about  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 exceeds 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 ll 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

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

here my session idle  time is more than 5 mins that why  oracle server kill 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 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  

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)
LIMIT                  VARCHAR2(40)

 

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 drop 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;