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

This article will explain in detail what are the parameters and usage of profile in Oracle. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

I. profile function

Profile in oracle system is used to limit the database resources that users can use.

2. Detailed explanation of profile parameters

(1) View database resource limit parameters

SQL > select * from dba_profiles

PROFILE RESOURCE_NAME RESOURCE LIMIT

-

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL UNLIMITED

DEFAULT CONNECT_TIME KERNEL UNLIMITED

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED

MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT

MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT

MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT

MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT

MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT

MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT

MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT

MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT

MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT

MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT

MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT

MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT

MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT

MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT

32 rows selected.

Unlimited: the user who allocates the profile has no restrictions on the use of resources. When using password parameters, unlimited means that there are no restrictions on the parameters.

Default: specifying default means ignoring some resource restrictions in profile. The initial definition of Default profile does not limit resources and can be changed through the alter profile command.

(2) detailed explanation of parameters

Resource_parameter section:

Session_per_user: specifies to limit the number of concurrent sessions for users.

Cpu_per_session: specifies the CPU time limit for the session, in 1% seconds.

Cpu_per_call: specifies the CPU time limit for one call (parse, execute, and extract) in 1% seconds.

Connect_time: specifies the total connection time for the session, in minutes.

Idle_time: specifies the total amount of time that a session is allowed to be continuously inactive, in minutes, after which the session will be disconnected.

Logical_reads_per_session: specifies the number of blocks allowed to be read in a session, including all blocks read from memory and disk.

Logical_read_per_call: specifies the maximum number of data blocks allowed to be read in a single SQL (parse, execute, and extract) call.

Private_sga: specifies the maximum amount of space, in bytes, that a session can allocate in a shared pool (SGA).

Composite_limit: specifies the total resource consumption of a session, expressed in service units 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 section:

Failed_login_attempts: specifies the maximum number of attempts to log in before the account is locked.

Password_life_time: specifies the number of days allowed for the same password.

Password_reuse_time and password_reuse_max: these two parameters must be related to 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 changes before the current password is reused. Both parameters must be set to integers.

Password_lock_time: specifies the lockout time of the account after the number of failed login attempts arrives, in days.

Password_grace_time: specify the number of days of grace, the number of days before the database issues a warning before the login expires. If the database password is not changed in between, the expiration will expire.

Password_verify_function: this field allows complex PL/SQL password validation scripts to be passed as parameters to create profile statements. The Oracle database provides a default script, but you can create your own validation rules or use third-party software to validate. For the Function name, you specify the name of the password authentication rule, and specifying Null means that the password authentication feature is not used. If you specify an expression for a password parameter, the expression can be in any format except the database quantum query.

Third, use profile

(1) create a profile with the following command:

CREATE PROFILE profile profile_nameLIMIT {resource_parameters | password_parameters} [resource_parameters | password_parameters]...

(2) profile is assigned to the user. The command is as follows:

Alter user user_name profile profile_name

Fourth, give examples

(1) create a user U1

SQL > show user

USER is "SYS"

SQL > create user U1 identified by U1

User created.

SQL > grant create session to U1

Grant succeeded.

(2) create profile as p1 to restrict users from being locked after two failed login attempts.

SQL > create profile p1 limit FAILED_LOGIN_ATTEMPTS 2

Profile created.

(3) assign the created p1 to U1

SQL > alter user U1 profile p1

User altered.

(4) try to verify

SQL > select USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE from dba_users where username='U1'

USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE

-the status of the U1 user is not locked.

U1 OPEN P1

SQL > conn u1/a1

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

/ / failed to log in twice

SQL > conn u1/a1

ERROR:

ORA-01017: invalid username/password; logon denied

SQL > select USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE from dba_users where USERNAME='U1'

USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE

-- / / check the status of U1, which has been locked

U1 LOCKED (TIMED) 05-MAR-14 P1

SQL > conn u1/u1

ERROR: / / after being locked, you can no longer log in with the correct password

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

(5) solution: the first method can be unlocked automatically after the number of days set by the Password_lock_time parameter has passed, the second kind needs the help of the administrator to unlock the user, and choose the second method to apply for a job administrator here.

SQL > show user

USER is "SYS"

SQL > alter user U1 account unlock identified by U1; / / users can use the previous password when unlocking

User altered.

SQL > conn u1/u1

Connected.

SQL > show user

USER is "U1" / / U1 user logged in successfully

So much for sharing about the parameters and usage of profile in Oracle. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.