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_profile 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 tom5 identified by tom5;
Assign the user to profile
SQL>alter user tom5 profile my_profile;
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 tom5
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_PROFILE';
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;
No comments:
Post a Comment