Oracle limits the number of connections and profiles for a user

Limit the number of links for a user, which can be achieved through Oracle's profile file

1. View the Resource_limit parameters:

Sql> Show Parameter Resource_limit

If False, set the Resource_limit parameter to True, that is, the resource limit is turned on:

Alter system set RESOURCE_LIMIT =true;

The change is not valid for the password resource and the password resource is always available

2. Create Profile:

Sql>create profile Sess limit sessions_per_user 20; --Maximum number of connections is limited to 20

3. Assign profile to the user:

Sql>alter user YDGWB profile Sess;

Attached: Profile Introduction

Oracle's profile is a means of restricting the use of resources by database users.

such as: Control the session or SQL can use the CPU, control the user's password management policy and so on. After the database is created, the system has a default profile named defaults, and if you do not specify it, the default is the one that is used by the user when the user is created.

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 interpretation

1, the database resources to make restrictions

{{Sessions_per_user number of concurrent sessions allowed per user name

| Cpu_per_session The total CPU time that a session can use, in 1% seconds

| Cpu_per_call a SQL call (parsing, executing, and fetching) allows CPU time to be used

| Connect_time Limit session connection time in minutes

| Idle_time time to allow idle sessions, in minutes

| Logical_reads_per_session limit the session to the block of data read, the unit is blocks

| Logical_reads_per_call restricts SQL calls to read blocks of data, in blocks

| Composite_limit "Combo Play"

} {integer | UNLIMITED | DEFAULT}

| PRIVATE_SGA restricting the allocation of private space in the SGA to the shared pool of a session {Size_clause | UNLIMITED | DEFAULT}

}

2. Restrict the password

{{failed_login_attempts the number of times an error can be attempted before the account is locked

| Password_life_time the number of days the password can be used, in days, the default value of 180 days

| Password_reuse_time Password Reusable time interval (combined with Password_reuse_max)

| Maximum number of Password_reuse_max password changes (combined with password_reuse_time)

| Password_lock_time number of days that the user is locked out after the number of error attempts, default 1 days

| Password_grace_time How many days you can 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;

such as: Alter profile default limit failed_login_attempts 100;

Delete Profile:drop profile [resource file name] [CASCADE];

If a profile is created that has been authorized to a user, the corresponding restriction is withdrawn using CASCADE Cascade, and the user is restricted by the system's default profile after the throttling information is retracted.

Set the effective time of the profile parameter

1, the user owns the profile in the password restrictions immediately effective, unrestricted. From this you can see how much Oracle attaches to user passwords.

2, the user owns the profile of the resource restrictions related to the setting of the Resource_limit parameter, when True, when False (the default value) set any value is invalid.

Sql> Show Parameter Resource_limit

NAME TYPE VALUE

-------------------- ----------- -------

Resource_limit Boolean FALSE

Oracle limits the number of connections and profiles for a user