Oracle修改用户密码错误次数及解锁用户

ORACLE11g错误密码登录默认是10次，10次错误后用户将被锁定，如下：

SQL> conn test/123456

ERROR: ORA-28000: the account is locked

 

1.查看被锁定的用户

SQL> select LOCK_DATE,username from dba_users where username='GXGAT';

LOCK_DATE USERNAME
--------- ------------------------------
16-JUN-20 TEST
LOCK_DATE为空说明没有锁定，非空为锁定。

或

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;

2.解锁用户方式：

登陆sys用户，执行解锁命令

SQL> alter user test account unlock;

User altered.

SQL> conn test/password

Connected.

 

3.现在我们来查看下默认连接错误锁定次数

SQL> select * from dba_profiles where resource_name like 'FAILED_LOGIN_ATTEMPTS%';

PROFILE RESOURCE_NAME RESOURCE            LIMIT
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------- -------- ---------------------------------------------------------------------------------------------------------------
COM INH IMP
--- --- ---
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
NO NO NO

 

4.修改默认的错误登录次数，为不限制（为安全起见，不建议使用）

SQL> alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

SQL>alter profile default limit failed_login_attempts 20;    //修改为20次