Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.