Oracle profile details

1. Specify to enable resource restrictions. This change is invalid for password resources and password resources are always available.

 

Alter system set resource_limit = true;

(Disabled by default)

 

2. Create a profile

 

Create profile test

Limit

Idle_time 30

Connect_time 480

;

Note: You must have the create profile permission to create a profile.

 

3. Specify the user profile

 

Alter user test profile test;

 

4. Modify the profile

 

Alter profile test limit idle_time 60;

 

5. Delete profile

 

Drop profile test

Drop profile test cascade;

 

Note:

1. the cascade option must be added when you delete an allocated profile.

2. You must have the create profile permission to create a profile.

3. default is the default profile and cannot be deleted.

 

6. Information Acquisition

 

Dba_profiles

Profile and Parameter Restriction values

Dba_users

Username, Profile

 

7. Detailed description of parameters

 

Kernel Resource (default: Unlimited ):

Composite_limit: Indicates the total resource consumption of a session, expressed in Service Units. The Oracle Database calculates the total service units of cpu_per_session, connect_time, logical_reads_per_session, and private-SGA in a favorable way.

 

Sessions_per_user: Limit the number of concurrent sessions of a user

 

Cpu_per_session: Defines the CPU usage time of each session. (Unit: 1/100 seconds)

 

Cpu_per_call: Specifies the CPU time limit for one call (resolution, execution, and extraction. (Unit: 1/100 seconds)

 

LOgical_reads_per_session: Specifies the number of data blocks that a session can read, including all data blocks read from memory and disk.

 

Logical_reads_per_call: Specifies the maximum number of data blocks that can be read by a single SQL statement (parsing, executing, and extracting) Call.

 

Idle_time: Specifies the total time (in minutes) that the session can be continuously inactive ). After this time, the session will be disconnected. However, long-running queries and other operations are not restricted.

 

Connect_time: Specifies the total connection time of the session. (Unit: minute)

 

Private_sga: Specify the maximum space (in bytes) that a session can allocate in the Shared Pool (SGA ).

Note: This limit is valid only when the Shared Server structure is used. The private space of a session in SGA includes private SQL and PL/SQL, but not shared SQL and PL/SQL.

 

Password Resource (default: Unlimited ):

Failed_login_attempts: Specifies the maximum number of logon attempts allowed before the account is locked.

 

Password_life_time:Specifies the number of days that a password can use. If the password_grace_time parameter is specified at the same time, if the password is not changed within grace period, the password will be invalid and the connection to the database will be rejected. If the password_grace_time parameter is not set, the default value of unlimited will throw a database warning, but allow the user to continue the connection.

 

Password_reuse_time and password_reuse_max: These two parameters must be associated with each other. password_reuse_time specifies the number of days before the password cannot be reused, while password_reuse_max specifies the number of times the password is changed before the current password is reused. Both parameters must be set as integers.

Note:

1. If an integer is specified for these two parameters, the user cannot reuse the password until the password is changed to the number of times specified by password_reuse_max and later within the time specified by password_reuse_time. For example, if password_reuse_time = 30 and password_reuse_max = 10, you can reuse the password after 30 days. The password must be changed more than 10 times.

2. If one of them is specified as an integer and the other is unlimited, the user can never reuse a password.

3. if one of them is specified as default, Oracle database uses the default value defined in profile. By default, all parameters are set as unlimited in profile. If the default value of profile is not changed, the database always defaults to unlimited.

4. If both parameters are set to unlimited, the database ignores them.

 

Password_lock_time: Specify the account lock time (in days) after the number of failed login attempts reaches ).

 

Password_grace_time: Specify the number of days before the database returns a warning. If the database password is not modified in the middle, it will expire.

 

Password_verify_function: This field allows you to pass the complex PL/SQL password verification script as a parameter to the create profile statement. Oracle Database provides a default script, but you can create your own verification rules or use third-party software for verification. For the function name, specify the name of the password verification rule. If it is null, the password verification function is not used. If an expression is specified for the password parameter, the expression can be in any format, except for database scalar queries.