Shulou(Shulou.com)06/01 Report--

When creating the database, where SYS logs in to the Oracle database, the SQL*Plus tool uses the following SQL to unlock the database.

1. Check the locked status of the Oracle account through the data dictionary dba_users, as follows: (v where OPEN means the account is unlocked; EXPIRED means the account is expired (you need to set a password to unlock it); LOCKED means the account is locked. )

2. Check which accounts are locked first.

Select username,account_status from dba_users

3. Use the ALTER USER statement to unlock the scott account:

ALTER USER scott ACCOUNT UNLOCK

Then use the ALTER USER statement to change the password for the scott account, as follows:

ALTER USER scott IDENTIFIED BY tiger

Check the status of the current scott account through the data dictionary dba_users, as follows: the query result shows that the scott account has been successfully unlocked. When using the data dictionary dba_users, it is important to note that the values of its fields are size-sensitive

Other strategies for related accounts

one

-1.

-- configure connect sys/password@db_link as sysdba for system parameters

Select * from dba_profiles where resource_name like 'FAILED_LOGIN_ATTEMPTS%'

-1 DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

-- continuous error connection 10 times the user is locked out

-- 2.

-- View locked users

Select LOCK_DATE,username from dba_users where username='USERNAME'

LOCK_DATE is empty means there is no lock, non-empty means lock.

-

SELECT S.USERNAME

DECODE (L.TYPE, 'TM',' TABLE LOCK', 'TX',' ROW LOCK', NULL) LOCK_LEVEL

O.OWNER

O.OBJECT_NAME

O.OBJECT_TYPE

S.SID

S.SERIAL#

S.TERMINAL

S.MACHINE

S.PROGRAM

S.OSUSER

FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O

WHERE S.SID = L.SID

AND O.OBJECT_ID = L.ID1

AND S.USERNAME IS NOT NULL

-3.

-- unlocking method

ALTER USER USER_NAME ACCOUNT UNLOCK

Note that during the upgrade process, the locked user may not be worth a

-- upgrade again

-set the default number of logins for the system

Alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS 10

Alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED

-

The data administrator sets the number of logins for the user separately to find the corresponding profile for the user, and then modify it. You can look at the user's creation language name to find the corresponding settings.

Profile in Oracle system can be used to limit the database resources that users can use. Use Create Profile command to create a Profile and use it to limit the use of database resources. If the profile is assigned to users, the database resources that users can use are within the limits of the profile.

2. Conditions:

To create a profile, you must have system permissions for CREATE PROFILE.

To specify resource limits for users, you must:

1. Use alter system dynamically or use the initialization parameter resource_limit to make the resource limit effective. This change is not valid for password resources, which 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 changed.

SQL > show parameter resource_limit

NAME TYPE VALUE

-

Resource_limit boolean TRUE

SQL >

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

3. Use the create user or alter user command to assign profile to the user.

Third, grammar:

CREATE PROFILE profile

LIMIT {resource_parameters | password_parameters} [resource_parameters | password_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}} |

{{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}} |

Fourth, grammatical explanation:

Profile: name of the configuration file. The Oracle database enforces resource restrictions in the following ways:

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

2. If the user attempts to perform an operation that exceeds other session resource limits, the database aborts the operation, rolls back the current transaction, and immediately returns an error. The user can then commit or roll back the current transaction and must end the session.

Tip: you can divide a piece into multiple segments, such as 1 hour (1 go 24 days) to limit the time, and you can specify a resource limit for the user, but the database will not implement the limit until the parameter takes effect.

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.

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. However, running queries and other operations for a long time is not subject to this restriction.

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). (this restriction is valid only when using a shared server structure, and the private space for sessions in SGA includes private SQL and PL/SQL, but does not include shared SQL and PL/SQL.)

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. If the password_grace_time parameter is also specified, if the password is not changed within the grace period, the password will expire and the connection to the database will be denied. If the password_grace_time parameter is not set, the default value of unlimited will trigger a database warning, but allow the user to continue to connect.

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.

1. If integers are specified for these two parameters, the user cannot reuse the password until the password has been changed the number of times specified by password_reuse_max within the time specified by password_reuse_time.

For example: password_reuse_time=30,password_reuse_max=10, users can reuse the password after 30 days, requiring that 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 you specify one of the default values defined in profile for the default,Oracle database, by default, all parameters are set to unlimited in profile, and if the default value of profile is not changed, the database always defaults to unlimited for that value.

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

Password_lock_time: specifies the shrinking 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, 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 any lattice, except for database scalar quantum queries.

5. Examples:

1. Create a profile:

Create profile new_profile limit password_reuse_max 10 password_reuse_time 30

2. Set the profile resource limit:

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 15k composite_limit 5000000

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

3. Set password limit profile:

Create profile app_users2 limit failed_login_attempts 5 password_life_time 60 password_reuse_time 60 password_reuse_max 5 password_verify_function verify_function password_lock_time 1/24 password_grace_time 10

4. Assign the profile to the user:

SQL > alter user dinya profile app_user

The user has changed.

SQL > alter user dinya profile default

The user has changed.

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.