Oracle limits the number of connections and PROFILE of a user

Oracle limits the number of connections and PROFILE of a user

Today, a colleague called to limit the number of connections of a user, which can be achieved through the Oracle PROFILE file.

1. View resource_limit parameters:
 
SQL> show parameter resource_limit
 
If this parameter is set to FALSE, set RESOURCE_LIMIT to TRUE to enable resource restriction:
 
Alter system set resource_limit = TRUE;
 
This change is invalid for password resources and password resources are always available
 
2. Create a PROFILE:
 
SQL> create profile sess limit sessions_per_user 20; -- the maximum number of connections is 20
 
3. Specify the PROFILE to the user:
 
SQL> alter user ydgwb profile sess;

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian
 
 
Appendix: PROFILE Introduction
 
Oracle profile files are a method to restrict database users from using resources.
 
For example, you can control the CPU that can be used by sessions or SQL statements, and control your password management policies. After the database is created, the system has a default profile named DEFAULT. If no special configuration is specified, the default profile used by the user when the user is created is DEFAULT.
 
1. View dba_profiles to find out which profiles are in the database.
 
SQL> select distinct profile from dba_profiles;
 
2. view all profiles
SQL> select * from dba_profiles order by PROFILE;
 
PROFILE RESOURCE_NAME RESOURCE LIMIT
 
--------------------------------------------------------------------
 
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
 
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
 
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
 
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
 
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
 
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
 
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
 
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
 
DEFAULT CONNECT_TIME KERNEL UNLIMITED
 
DEFAULT IDLE_TIME KERNEL UNLIMITED
 
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
 
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
 
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
 
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
 
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
 
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
 
Parameter description
 
1. Restrict database resources
 
{SESSIONS_PER_USER: number of concurrent sessions allowed by each user name
 
| CPU time that a CPU_PER_SESSION session can use in total, in the unit of 1% seconds
 
| CPU time allowed for one SQL call (parsing, execution, and retrieval) of CPU_PER_CALL
 
| CONNECT_TIME limits the session connection time, in minutes
 
| IDLE_TIME: the time allowed for idle sessions. The unit is minute.
 
| LOGICAL_READS_PER_SESSION restricts the session's reading of data blocks. The unit is block.
 
| LOGICAL_READS_PER_CALL restricts the reading of data blocks by SQL calls. The unit is block.
 
| COMPOSITE_LIMIT"
 
} {Integer | UNLIMITED | DEFAULT}
 
| PRIVATE_SGA limits the allocation of private space in the Shared Pool of the session in SGA {size_clause | UNLIMITED | DEFAULT}
 
}
 
2. Restrict passwords
 
{Number of failed attempts before the FAILED_LOGIN_ATTEMPTS account is locked
 
| Number of days in which the PASSWORD_LIFE_TIME password can be used. The unit is day. The default value is 180 days.
 
| Interval at which PASSWORD_REUSE_TIME passwords can be reused (combined with PASSWORD_REUSE_MAX)
 
| Maximum number of PASSWORD_REUSE_MAX password changes (combined with PASSWORD_REUSE_TIME)
 
| PASSWORD_LOCK_TIME indicates the number of days that the user is locked after the number of failed attempts is exceeded. The default value is 1 day.
 
| PASSWORD_GRACE_TIME how many days can I use the original password after the password expires?
 
} {Expr | UNLIMITED | DEFAULT}
 
| PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT}
 
}
 
Modify profile: alter profile [resource file name] limit [Resource Name] unlimited;
 
For example, alter profile default limit failed_login_attempts 100;
 
Delete PROFILE: drop profile [resource file name] [CASCADE];
 
If the created PROFILE has been authorized to a user, use CASCADE to unbind the corresponding restriction. After revoking the restriction information, use the default PROFILE to restrict the user.
 
Set the effective time of the PROFILE Parameter
 
1. Password restrictions in all profiles of a user take effect immediately without any restrictions. From this we can see that Oracle attaches great importance to user passwords.
 
2. Resource restrictions in all profiles of a user are related to the resource_limit parameter settings. This parameter takes effect when it is set to TRUE. If it is set to FALSE (default), any value is invalid.
 
SQL> show parameter resource_limit
 
NAME TYPE VALUE
 
--------------------------------------
 
Resource_limit boolean FALSE
 
The above PROFILE content is taken from Baidu Library