ORACLE profile
Configuration file functions: manage passwords, limit the resources that sessions can occupy. Oracle recommends using Resource Manager to limit resource management.
1. Grammar:
Create a profile:
CREATE PROFILE profile LIMIT { resource_parameters | password_parameters }...;
Modify configuration file:
ALTER PROFILE profile LIMIT { resource_parameters | password_parameters }...;
Delete profile:
drop profile profile_name [cascade];
Application Profile:
alter user user_name profile profile_name;
2. related parameters
1) Management password:
failed_login_attempts: Passwords can be entered incorrectly for consecutive times before locking the user.
password_lock_time: Number of days the account is locked due to incorrect password input;
password_life_time: number of days before password expires;
password_grace_time: the number of days after the password expires that you can still log in;
password_reuse_time: the time interval between reuses of the same password, in days;
password_reuse_max: The number of times a password must be changed before it can be used again after PASSWORD_REUSE_TIME has been reached.
password_verify_function: password complexity.
2) Resource constraints:
SESSIONS_PER_USER: Number of sessions each user can log in to;
CPU_PER_SESSION: Specifies the CPU time limit for the session, in hundredths of a second.
CPU_PER_CALL CPU time limit in hundredths of a second.
CONNECT_TIME Connection time in minutes
IDLE_TIME Specifies the allowable period of inactivity during the session, expressed in minutes. Long-running queries and other operations are exempt from this restriction.
LOGICAL_READS_PER_SESSION Specifies the allowable number of blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL specifies the allowable number of data blocks read for a call to process SQL statements (parse, execute, or extract).
PRIVATE_SGA specifies the amount of private space a session can allocate in the shared pool of the system global area (SGA).
Resource restrictions apply only if instance parameters are set:
alter system set resource_limit=true;
3. example
3.1 create a new profile
Create a new profile for testing.
CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER 3
CONNECT_TIME 2
PASSWORD_REUSE_MAX 2;
3.2 View profile types
SQL> select distinct profile from dba_profiles;
PROFILE
------------------------------
MONITORING_PROFILE
APP_USER
DEFAULT
3.3 View the contents of a profile
SQL> select * from dba_profiles where profile='APP_USER';#unassigned value, LIMIT field is DEFAULT.
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT------------------------------ -------------------------------- ------------- ----------------------------------------APP_USER COMPOSITE_LIMIT KERNEL DEFAULTAPP_USER SESSIONS_PER_USER KERNEL 3APP_USER CPU_PER_SESSION KERNEL DEFAULTAPP_USER CPU_PER_CALL KERNEL DEFAULTAPP_USER LOGICAL_READS_PER_SESSION KERNEL DEFAULTAPP_USER LOGICAL_READS_PER_CALL KERNEL DEFAULTAPP_USER IDLE_TIME KERNEL DEFAULTAPP_USER CONNECT_TIME KERNEL 2APP_USER PRIVATE_SGA KERNEL DEFAULTAPP_USER FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULTAPP_USER PASSWORD_LIFE_TIME PASSWORD DEFAULTAPP_USER PASSWORD_REUSE_TIME PASSWORD DEFAULTAPP_USER PASSWORD_REUSE_MAX PASSWORD 2APP_USER PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULTAPP_USER PASSWORD_LOCK_TIME PASSWORD DEFAULTAPP_USER PASSWORD_GRACE_TIME PASSWORD DEFAULT
3.4 Modify default profiles for SCOTT users
SQL> alter user scott profile app_user;
User altered
#Enable resource restrictions
alter system set resource_limit=true;
#Log in to scott, connect more than 3 sessions will report an error
SQL> conn scott/tiger
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
#After 2 minutes of connection, execute query operation error; if there is no operation, scott user still cannot log in.
SQL> select count(1) from tabs;
select count(1) from tabs
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02399: exceeded maximum connect time, you are being logged off
#change scott's password to tiger
SQL> alter user scott identified by scott;
User altered
SQL> alter user scott identified by scott;
alter user scott identified by scott
ORA-28007: Unable to reuse password
