Oracle User Unlocked

Tag:com    change    int    rollback    idle    Disk     server     own    serial   

-1. --System parameter configuration connect Sys/[email protected]_link as SYSDBA select * from Dba_profiles where resource_name like ' failed_l　　Ogin_attempts% ';　　--1 DEFAULT failed_login_attempts PASSWORD 10--Consecutive error connection 10 times the user is locked--2.　　--View locked user select Lock_date,username from dba_users where username= ' username ';　　Lock_date NULL indicates no lock, non-null is locked. -----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_OBJE　　CTS O WHERE s.sid = l.sid and o.object_id = L.id1 and s.username are not NULL;　　--3.　　--Unlocking Method ALTER USER user_name account UNLOCK;　　--value of attention, during the upgrade process, the locked user, may not be worth a--re-upgrade-----Set the default login number of the system alter profile defaults limit failed_login_attempts 10;　　Alter profile DEFAULT limit failed_login_attempts UNLIMITED;　　------------The Data Administrator sets the number of logins for this user individually, you will find the profile for that user and then modify it. You can view the user's creation name to find the corresponding setting. Profiles in an Oracle system can be used to limit the database resources that users can use, using the Create PThe Rofile command creates a profile that uses it to restrict the use of database resources, and if it is assigned to a user, the database resources that the user is able to use are within the limits of the profiles.　　Second, the condition: Create profile must have the system permissions for creating profile. To specify resource limits for users, you must: 1. Use the alter system dynamically or use the initialization parameter Resource_limit to make resource restrictions effective.　　The change is not valid for the password resource and the password resource is always available. Sql> Show parameter Resource_limit NAME TYPE VALUE ———————————— ——— – —————————— resource_limit boolean FALSE sql&gt ;　　alter system set resource_limit=true;　　The system has changed.　　Sql> Show parameter resource_limit; NAME TYPE VALUE ———————————— ——— – —————————— resource_limit boolean TRUE sql> 2.　　Create a profile that defines a limit on database resources by using creation profile. 3.　　Use the CREATE user or alter USER command to assign the profile to the user. Iii. syntax: CREATE profile LIMIT {resource_parameters | password_parameters} [resource_parameters | password_paramet ERS] ...; <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}} Four, syntax explanation: Profile: The name of the configuration file. The Oracle Database forces resource throttling 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. The user executes the command again, and the database returns an error, 2. If the user tries to perform an operation that exceeds the other session resource limit, the database discards the operation, rolls back the current transaction, and returns an error immediately.　　After the user can commit or roll back the current transaction, the session must end.　　Tip: You can limit the time by dividing one piece into multiple segments, such as 1 hours (1/24 days), and you can specify resource limits for the user, but the database will not enforce the limit until the parameters are valid.　　Unlimited: The user assigning this profile has unrestricted access to the resource, and when the password parameter is used, Unlimited means that there is no restriction on the parameters.　　Default: Specifying default means ignoring some resource restrictions on the profile, and the default profile initial definition is not limited to resources and can be changed by the Alter Profile command.　　Resource_parameter section: Session_per_user: Specifies the number of concurrent sessions that limit 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 invocation (parse, execute, and fetch) in 1% seconds.　　Connect_time: Specifies the total connection time, in minutes, for the session. Idle_time: Specifies the total time, in minutes, that the session is allowed to be continuously inactive, and the session will be disconnected.　　However, long-running queries and other operations are not subject to this limitation.　　Logical_reads_per_session: Specifies the number of blocks of data that a session allows to read, including all blocks of data read from memory and disk. Logical_read_per_call: Specifies the maximum number of data blocks that are allowed to be read by the Execute SQL (parse, execute, and fetch) call at one time. PRIVATE_SGA: Specifies the maximum amount of space, in bytes, that a session can allow to allocate in the shared pool (SGA).　　(This restriction is only valid when using the shared server structure, where the private space of the session in the SGA includes both private and PL/SQL, but not shared SQL and PL/SQL). Composite_limit: Specifies the total resource consumption for a session, expressed in service units units. Oracle database calculates Cpu_per_session,connect_time, logical_reads_per_session, and PRIVATE-SGA total service units in an advantageous manner Password_　　Parameter section: failed_login_attempts: Specifies the maximum number of times that an attempt to log in is allowed before the account is locked out. Password_life_time: Specifies the number of days that the same password is allowed to use. If the Password_grace_time parameter is specified at the same time, if the password is not changed within grace period, the password is invalidated and the connection database is rejected.　　If the Password_grace_time parameter is not set, the default value of unlimited raises a database warning, but allows 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, and password_reuse_ MAX Specifies the number of times the password has changed before the current password is reused.　　Two parameters must be set to an integer. 1.　　If an integer is specified for both parameters, the user cannot reuse the password until the password has been changed Password_reuse_max the specified number of times after the specified amount of time in Password_reuse_time.　　For example: password_reuse_time=30,password_reuse_max=10, the user can reuse the password after 30 days, requiring the password to be changed more than 10 times. 2.　　If one of these 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, all parameters are set to unlimited in profiles by default, if not changed ProfilE default value, which is always default to unlimited for the database. 4.　　If all two parameters are set to unlimited, the database ignores them.　　Password_lock_time: Specifies the set time, in days, for the number of failed login attempts to reach payback households. Password_grace_time: Specifies the number of days to expire, and the database issues a warning to the number of days before the login expires.　　If the database password is not modified in the middle, the expiration is invalidated. Password_verify_function: This field allows complex PL/SQL password validation scripts to be passed as parameters to the Create profile statement. The Oracle database provides a default script, but you can create your own validation rules or use third-party software validation. For a function name, specifying the name of the password validation rule, which is specified as NULL, means that the password verification feature is not used.　　If you specify an expression for the password parameter, the expression can be in any format except for the database-scalar subquery. Five, example: 1.　　Create a profile:create profile New_profile limit Password_reuse_max Password_reuse_time 30; 2. Set Profile resource limit: Create profile App_user limit Sessions_per_user Unlimited cpu_per_session unlimited Cpu_per_call　　Ect_time logical_reads_per_session default Logical_reads_per_call 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 password_reuse_time Ord_reuse_max 5 password_verify_function verify_function Password_lock_time 1/24 Password_grace_time 10; 4.　　Assigning profiles to users: sql> alter user Dinya profile App_user;　　The user has changed.　　sql> alter user Dinya profile default;　　The user has changed. The reason and method of the Oracle user being locked is told that the test user was locked 1, logged in with DBA role, unlocked, and set the specific time format to see the time sql> alter session set Nls_date_format= '　　Yyyy-mm-dd Hh24:mi:ss ';　　Session altered.　　2. View the specific lock time sql> select Username,lock_date from dba_users where username= ' TEST '; USERNAME lock_date-------------------------------------------------TEST 2009-03-10 08:51:03 3, unlock sql> alter user　　Test account unlock;　　User altered. 4, check that the IP caused by the test user is locked view $oracle_home/network/admin/log/listener.log log 10-mar-2009 08:51:03 * (Connect_data= (SID= lhoms) (server=dedicated) (Cid= (program=oracle) (HOST=OMSTESTDB) (user=oraoms)) * (Address= (protocol=tcp) (HOST= 10.69.1.11) (port=49434)) * Establish * lhoms * 0 10-mar-2009 08:51:03 * (Connect_data= (sid=lhoms) (server=dedicated) (CID= (program=oracle) (HOST=OMSTESTDB) (user=oraoms))) * (Address= (PROTOCOL=TCP) (host=10.69.1.11) (PORt=49435) * Establish * lhoms * 0 exam to the exam big so that is the above 10.69.1.11 IP attempt multiple failed landing caused by the lock note: The general database default is 10 attempts to lock the user 1, view Failed_login_at　　tempts Value SELECT * FROM Dba_profiles 2, modified to 30 times ALTER profile default limit failed_login_attempts 30; 3, modified to unlimited (for security purposes, not recommended)

Alter profile default limit Failed_login_attempts unlimited;

Oracle User Unlocked