The second day of Oracle learning --- use of Profile, oracle --- profile

The second day of Oracle learning --- use of Profile, oracle --- profile

Environment: Oracle 11g

1. Transfer Permissions

1. If the object permission is passed, add with grant option later;

Eg: If the system user has a temp table and only wants the usertest user to have the query permission, then:

grant select on temp to usertest with grant option;

2. If the system permission is passed, add the with admin option;

Eg: The system user passes the connect role to the usertest User:

grant connect to usertest with admin option;

Ii. Detailed description of the use of Profile

1. Purpose:

The Profile in the Oracle database system can be used to restrict the database resources that users can use. You can use the create Profile command to create a Profile to restrict the use of database resources. If you allocate a Profile to a user, all the database resources that the user can use are limited by the Profile.

2. conditions:

You must have dba permissions to configure the Profile file.

3. Syntax:

Create profile profile

LIMIT {resource_parameters

 

| Password_parameters

 

}

 

[Resource_parameters

 

| Password_parameters

 

]...;

 

<Resource_parameters>

 

{SESSIONS_PER_USER

 

| CPU_PER_SESSION

 

| CPU_PER_CALL

 

| CONNECT_TIME

 

| IDLE_TIME

 

| LOGICAL_READS_PER_SESSION

 

| LOGICAL_READS_PER_CALL

 

| COMPOSITE_LIMIT

 

}

 

{Integer | UNLIMITED | DEFAULT}

 

| PRIVATE_SGA

 

{Integer [K | M] | UNLIMITED | DEFAULT}

 

}

4. Syntax explanation:

Resource_parameter
Session_per_user: specifies the number of concurrent sessions of a user.
Cpu_per_session: Specifies the CPU time limit of the session, in the unit of 1% seconds.
Cpu_per_call: Specifies the CPU time limit for one call (resolution, execution, and extraction), in 1% seconds.
Connect_time: Specifies the total connection time of a session, in minutes.
Idle_time: Specifies the total time for a session to allow continuous inactivity, in minutes. If the duration is exceeded, the session will be disconnected. However, long-running queries and other operations are not restricted.
Logical_reads_per_session: specify the number of data blocks that a session can read, including all data blocks read from memory and disk.
Logical_read_per_call: specify the maximum number of data blocks that can be read when an SQL statement (parsing, execution, and extraction) call is executed.
Private_sga: specify the maximum space that a session can allocate in the Shared Pool (SGA), in bytes. (This restriction is only valid 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 ).
Composite_limit: Specifies the total resource consumption of a session, in the unit of 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.

Password_parameter part:
Failed_login_attempts: specifies the maximum number of logon attempts allowed before the account is locked.
Password_life_time: specifies the number of days allowed by the same password. 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. 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 has changed before the current password is reused. Both parameters must be set as integers.
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 contraction time after the number of failed login attempts reaches, in days.
Password_grace_time: specifies 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 complex PL/SQL password verification scripts as parameters 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.

5. instance:

Instance 1> User test is limited to three attempts to enter the password. If three attempts are incorrect, the account is locked for three days:

 

To unlock an account, use the following statement:

SQL> alter user usertest (your_user_name) account unlock;

Instance 2> force the user to change the password periodically. Change the login password of usertest every 10 days. The grace period is 2 days:

Instance 3> you do not need to use the previous password when changing the password:

With the password history, the Oracle database will store the password modification information in the data dictionary, so that when the user changes the password, the New and Old passwords will be matched and found consistent, remind the user to re-enter the password

6. Delete Profile configuration

drop profile your_profile_name [cascade];