ORACLE Account Tips EXPIRED (GRACE) Problem Solving
2013-01-30 15:09:24
Tags: Oracle user
Original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://alexy.blog.51cto.com/6115453/1129751
Recently a strange phenomenon for account of expired (GRACE), access to information that this is a oracle11g security update, similar to the system account expires.
1) Query the user status as
Col username for A20
Col Account_status for A20
sql> Select Username,account_status,lock_date,expiry_datefrom user_users;
USERNAME account_status lock_date expiry_date
-------------------- ---------------------------------- --------------
CBD EXPIRED (GRACE) April-February-13
1) Enquiry Form Dba_profiles
sql> SELECT *from dba_profiles WHERE profile= ' DEFAULT ' and resource_name like ' password% ';
Profile Resource_name Resourcelimit
-------------------- -------------------------------- ------------------------------------------------
DEFAULT Password_life_time PASSWORD 180
DEFAULT Password_reuse_time passwordunlimited
DEFAULT Password_reuse_max PASSWORD UNLIMITED
DEFAULT password_verify_function PASSWORD NULL
DEFAULT password_lock_time PASSWORD 1
DEFAULT Password_grace_time PASSWORD7
6 rows selected.
Sql>
The explanations are as follows:
The failed_login_attempts integer sets the number of failures that can fail when logging on to the Oracle database. Once a user attempts to log in to the database, the user's account is locked and can only be unlocked by the DBA.
Password_life_time the valid time (in days) for the password to be set, the password must be reset once it is over. The default is unlimited.
Password_reuse_time Many systems do not allow users to re-enable passwords that have been used in the past. This resource entry sets the number of days after which a failed password is passed before the user can re-use the password. The default is unlimited.
Password_reuse_max the number of times the password must be reset before re-enabling a previously used password (number of repetitions).
Password_lock_time sets the number of days the account is locked (when the logon failure reaches failed_login_attempts).
Password_grace_time set a Grace day to reset the password before the password expires. When the password expires, a warning message appears at logon to show the number of days. If the password is not modified within the Grace Day, the password is invalidated.
Password_verity_function This resource entry allows you to invoke a PL/SQL to verify the password. Oracle has provided scripts for the app, but users can develop their own validation scripts whenever they want. The parameter is set to the name of the PL/SQL function. The default is null.
2) default valid for 180 days
Sql> Select *from dba_profiles where profile= ' DEFAULT ' andresource_name= ' password_life_time ';
Profile Resource_name Resourcelimit
-------------------- -------------------------------- ------------------------------------------------
DEFAULT Password_life_time PASSWORD 180
Sql>
2) Default Grace time is 7 days
Sql> SELECT *from dba_profiles s WHERE s.profile= ' DEFAULT ' andresource_name= ' password_grace_time ';
Profile Resource_name Resource_type LIMIT
-------------------- -------------------- ---------------- --------------------
DEFAULT Password_grace_time PASSWORD 7
Sql>
3) Modify the default validity period for unlimited days
Sql>alter profile DEFAULT Limitpassword_life_time UNLIMITED;
The user has changed.
Sql>
4) Modify the default grace period
Sql>alterprofile DEFAULT LIMIT password_grace_time UNLIMITED;
The user has changed.
Sql>
5) Change the user password to the original password (assuming the current password is CBD_ABC)
Sql>alter Usercbs identified by cbd_123;
The user has changed.
Sql>
6) View the status of the user in the current period
Sql> selectusername,account_status,lock_date,expiry_date from User_users;
USERNAME account_status lock_date expiry_date
-------------------- -------------------- -------------- --------------
CBD OPEN
Sql>
To this, the account expiration issue is resolved.
About account status Issues supplement
1) Query user profile status
Sql> selectusername,profile from dba_users where username like ' cbd% ';
USERNAME profile
------------------------- --------------------
CBD DEFAULT
Sql>
2) How many states are there in the account?
sql> Select *from user_astatus_map;
status# STATUS
---------- ----------------------------------------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED (GRACE)
4 LOCKED (TIMED)
8 LOCKED
5 EXPIRED & LOCKED (TIMED)
6 EXPIRED (GRACE) & LOCKED (TIMED)
9 EXPIRED & LOCKED
10EXPIRED (GRACE) & LOCKED
10 Class Status Description:
The five basic states can be divided into three categories:
- normal state;
- Locked state;
- Password expiration status.
1. The open state indicates that the user is in a normal state.
2, locked, and Locked (TIMED) indicate that the user is locked out. There are two types of cases:
- The DBA explicitly locks the user through the SQL statement;
- Passive locking, by default if the password input error more than 10 times the lock;
This restriction is controlled by the failed_login_attempts in profile and can be viewed in view dba_profiles.
DBA explicitly locks user locked
Alter USER [username] account lock;
Passive lock locked (TIMED) after entering 10 times wrong password
This restriction is controlled by the failed_login_attempts in profile, which can be queried through the Dba_profiles view
3, expired and expired (GRACE) indicate user password expiration status.
Modify Password_life_time in profile to implement password expiration
Alter profile default limit Password_life_time unlimited;
After the password expires, you can also modify the number of days that Password_grace_time control uses in the profile:
Alter profile default limit Password_grece_time 180;
For users with password expiration open:a
Alter user [username] identified by <password>account unlock;
Recommended Posts:
http://blog.csdn.net/kai27ks/article/details/6270350
http://hi.baidu.com/nonyi_com/item/f29f10720eae1c41ee1e534a
This article is from the "Focus on Oracle" blog, so be sure to keep this source http://alexy.blog.51cto.com/6115453/1129751
"Go" Oracle Account Tip expired (GRACE) Problem resolution