oracle用户锁定问题

1.查询允许用户密码连续输入错误次数，超过次数就会锁定该用户

SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7



2. 2.查询用户锁定状态

SQL> select https://www.360docs.net/doc/341481063.html,ername,u.account_status,t.lcount,u.profile from dba_users u,user$ t where https://www.360docs.net/doc/341481063.html,=https://www.360docs.net/doc/341481063.html,ername and https://www.360docs.net/doc/341481063.html,='SCOTT';

USERNAME ACCOUNT_STATUS LCOUNT PROFILE

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

SCOTT OPEN 1 DEFAULT



3. 3.尝试10次错误密码输入

SQL> conn scott/12

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.



4. 4.查询用户锁定状态

SQL> select https://www.360docs.net/doc/341481063.html,ername,u.account_status,t.lcount,u.profile from dba_users u,user$ t where https://www.360docs.net/doc/341481063.html,=https://www.360docs.net/doc/341481063.html,ername and https://www.360docs.net/doc/341481063.html,='SCOTT';

USERNAME ACCOUNT_STATUS LCOUNT PROFILE

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

SCOTT LOCKED(TIMED) 10 DEFAULT



5. 5.用户解锁

SQL> alter user scott account unlock;

User altered.

注：如果忘记密码重新设置

SQL> alter user scott identified by tiger ;

User altered.



6. 6.查询用户解锁状态

SQL> select https://www.360docs.net/doc/341481063.html,ername,u.account_status,t.lcount,u.profile from dba_users u,user$ t where https://www.360docs.net/doc/341481063.html,=https://www.360docs.net/doc/341481063.html,ername and https://www.360docs.net/doc/341481063.html,='SCOTT';

USERNAME ACCOUNT_STATUS LCOUNT PROFILE

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

SCOTT OPEN 0 DEFAULT



7. 7.解除密码错误输入次数限制

SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

Profile altered.



8. 8.查询用户密码错误输入次数限制

SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY

_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7