Shulou(Shulou.com)05/31 Report--

Editor to share with you how Oracle limits the number of connections of a user, I believe most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's learn about it!

To limit the number of links for a user, you can do so through the PROFILE file of ORACLE

1. View the resource_limit parameters:

SQL > show parameter resource_limit

If FALSE, set the RESOURCE_LIMIT parameter to TRUE, that is, enable the resource limit:

Alter system set resource_limit = TRUE

This change is not valid for password resources, which are always available

two。 Create a PROFILE:

SQL > create profile sess limit sessions_per_user 20;-the maximum number of connections is limited to 20

3. Assign PROFILE to the user:

SQL > alter user ydgwb profile sess

Attached: introduction of PROFILE

ORACLE's PROFILE file is a means of limiting the use of resources by database users.

Such as: control the CPU that session or sql can use, control the password management policy of users, and so on. After the database is created, the system has a default PROFILE named DEFAULT. If not specified, the default PROFILE used by the user when creating the user is DEFAULT.

1. Look at the view dba_profiles to find out what PROFILE is in the database.

SQL > select distinct profile from dba_profiles

two。 View all PROFILE

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. Make restrictions on database resources

{{number of parallel sessions allowed per user name of SESSIONS_PER_USER

| | CPU_PER_SESSION the total CPU time available for a session (in 1% seconds) |

| | CPU time allowed for a SQL call to CPU_PER_CALL (parsing, executing, and obtaining) |

| | CONNECT_TIME limits session connection time (in minutes) |

| | time allowed for idle sessions in IDLE_TIME (in minutes) |

| | LOGICAL_READS_PER_SESSION restricts the read of data blocks by the session (in blocks) |

| | LOGICAL_READS_PER_CALL restricts the reading of data blocks by SQL calls (in blocks) |

| | COMPOSITE_LIMIT "combination play"

} {integer | UNLIMITED | DEFAULT}

| | PRIVATE_SGA restricts the allocation of private space for sessions in SGA Shared Pool {size_clause | UNLIMITED | DEFAULT} |

}

2. Restrict the password

{{number of error attempts that can be made before the FAILED_LOGIN_ATTEMPTS account is locked out

| | number of days in which the PASSWORD_LIFE_TIME password can be used (in days). The default is 180 days. |

| | interval between reusable PASSWORD_REUSE_TIME passwords (combined with PASSWORD_REUSE_MAX) |

| | maximum number of PASSWORD_REUSE_MAX password changes (combined with PASSWORD_REUSE_TIME) |

| | the number of days for which a user is locked out after PASSWORD_LOCK_TIME exceeds the number of error attempts. Default is 1 day. |

| | PASSWORD_GRACE_TIME how many days after the password expires, you can use the original password |

} {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 the created PROFILE has been authorized to a user, use the CASCADE cascade to withdraw the corresponding restrictions. After withdrawing the limit information, the user will be restricted with the system default PROFILE.

Set the effective time of the PROFILE parameter

1. The restrictions on passwords in PROFILE owned by users will take effect immediately and are not restricted. From this, we can see how much Oracle attaches importance to the user's password.

2. The restrictions on resources in all PROFILE owned by users are related to the setting of the resource_limit parameter, which takes effect when it is TRUE, and any value is invalid when it is FALSE (default).

SQL > show parameter resource_limit

NAME TYPE VALUE

Resource_limit boolean FALSE

The above is all the contents of the article "how Oracle limits the number of connections to a user". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.