ORACLE user PROFILE and oracleprofile

ORACLE user PROFILE and oracleprofile

ORACLEUserPROFILEDetails

I. Official Website description

Oraclerecommends that you use the Database Resource Manager rather than the SQLstatement to establish resource limits. The Database Resource Manager offers amore flexible means of management and tracking resource use.

Purpose:

Use the create profile statement to create a profile, which is a set oflimits on database resources. If you assign the profile to a user, then thatuser cannot exceed these limits.

The profile in the Oracle system can be used to restrict the database resources that users can use. Use the create profile command to create a profile to restrict the use of database resources. If the profile is assigned to the user, all the database resources that the user can use are limited by the profile.

Prerequisites:

To create aprofile, you must have the create profile system privilege.

To specifyresource limits for a user, you must:

(1). Enable resource limits dynamically with the alter system statement or with the initializationparameter RESOURCE_LIMITS. This parameter does not apply to password resources. Password resources are always enabled.

(2). Create aprofile that defines the limits using the create profile statement.

(3). Assign theprofile to the user using the create user or alter user statement.

2. PROFILE management

Profile is a set of names for password and resource restrictions. When an oracle database is created, oracle automatically creates a profile named DEFAULT. The initialized DEFAULT has no password or resource restrictions.

1. Note the following when using PROFILE:

(1) When creating a PROFILE, if only some passwords or resource restriction options are set, other options will automatically use the DEFAULT value (corresponding to the DEFAULT option ).

(2) If the PROFILE option is not specified when a user is created, oracle automatically assigns the DEFAULT option to the corresponding database user.

(3) One user can only allocate one PROFILE. If you want to manage your passwords and resources at the same time, you must specify both the password and resource options when creating a PROFILE.

(4) When you use PROFILE to manage passwords, the password management option is always activated. However, if you use PROFILE to manage resources, you must activate resource restrictions.

2. Conditions

To create a profile, you must have the CREATEPROFILE system permission.

(1) dynamically use altersystem or use the initialization parameter resource_limit to make the resource limit take effect. This change is invalid for password resources and password resources are always available.

SQL> show parameter resource_limit

NAME TYPE VALUE

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

Resource_limit boolean FALSE

SQL> alter system set resource_limit = true;

The system has been changed.

SQL> show parameter resource_limit

NAME TYPE VALUE

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

Resource_limit boolean TRUE

 

(2) Use create profile to create a profile that defines the restrictions on database resources.

SQL> create profile db_pro limit idle_time 30connect_time 480;

IDLE_TIMESpecify the permitted periods of continuousinactive time during a session, expressed in mimutes (minutes). Long-running queries andother operations are not subject to this limit.

CONNECT_TIME Specifythe total elapsed time limit for a session, expressed in minutes.

Other resources such:

Session_per_user limits the connections that can be performed by a user name

Cpu time of the server that the user can use in one session of cpu_per_session (whitelist)

Cpu_per_call: indicates the time in which the user process calls the database once (in milliseconds)

Private_sga limits memory usage by one user

(3) Use the create user or alter user command to allocate the profile to the user.

SQL> create user lxh identified by lxh profile test;

The user has been created. -- Specify the profile when creating a user

SQL> alter user lxh profiletest; -- modify the user's profile

SQL> alter user lxh profiledefault; -- restore the default value

(4) modify the PROFILE

SQL> alter profile test limit idle_time 60;

Configuration File changed

(5) delete a PROFILE

SQL> drop profile test;

The configuration file has been deleted.

SQL> 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.

 

Iii. Profile creation syntax and Explanation

 

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}

}

<Password_parameters>

{FAILED_LOGIN_ATTEMPTS

| PASSWORD_LIFE_TIME

| PASSWORD_REUSE_TIME

| PASSWORD_REUSE_MAX

| PASSWORD_LOCK_TIME

| PASSWORD_GRACE_TIME

}

{Expr | UNLIMITED | DEFAULT}

| PASSWORD_VERIFY_FUNCTION

{Function | NULL | DEFAULT }}

 

Iv. Syntax explanation

 

Profile: name of the configuration file. Oracle databases force resource restrictions in the following ways:

1. If the user exceeds the session resource limit of connect_time or idle_time, the database rolls back the current transaction and ends the session. When the user executes the command again, the database returns an error,

2. If you attempt to perform operations that exceed other session resource limits, the database will discard the operation, roll back the current transaction, and immediately return an error. After that, you can submit or roll back the current transaction. You must end the session.

Tip: You can divide a row into multiple segments, such as one hour (1/24 days) to limit the time. You can specify resource restrictions for users, but the database will only execute the restrictions after the parameters take effect.

Unlimited: the user who assigns the profile has no limit on the resource usage. When the password parameter is used, unlimited means that there is no limit on the parameter.

Default: if it is set to default, some resource restrictions on the profile are ignored. The initial definition of the Default profile is not limited to resources and can be changed through the alterprofile command.

 

Resource_parameter part:

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.

 

V. Example:

1. Create a profile:

Createprofile new_profile

Limit password_reuse_max 10

Password_reuse_time 30;

 

2. Set profile resource restrictions:

Create profile app_user limit

Sessions_per_user unlimited

Cpu_per_session unlimited

CPU _ per_call 3000

Connect_time 45

Logical_reads_per_session default

Logical_reads_per_call 1000

Private_sga 15 k

Composite_limit 5000000;

The total resourcecost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the alter resource cost statement.

3. Set Password restriction profile:

Createprofile app_users limit

Failed_login_attempts 5

Password_life_time 60

Password_reuse_time 60

Password_reuse_max 5

Password_verify_function verify_function

Pass word_lock_time 1/24

Password_grace_time 10;

4. Allocate the configuration file to the user:

SQL> alter user dinya profile app_user;

The user has changed.

SQL> alter user dinya profile default;

The user has changed.

 

6Change Password

1, QueryUseRPassword

SQL> select user #, name, password from user $ where name = 'sdxj ';

85 SDXJ 666F8605B1CA3CFF

2, Change Password

SQL> alter user sdxj identified by values '666f8605b1ca3cff ';