CNDBA社区
Orcle 12c DG 新特性---Active Data Guard Enhanced Security
作者：
Expect-乐
1 说明
从12c开始，新增一个视图v$RO_USER_ACCOUNT，用于跟踪动态用户尝试登录失败次数的信息。该特性只对备库有效，如果该用户在备库由于登录失败次数超过PROFILE的限制而锁定。那么不会影响主库该用户的使用。
提高了安全性。
| Column |
Datatype |
Description |
| USERID |
NUMBER |
User ID number |
| PASSW_EXPIRED |
NUMBER |
Indicates whether the password has expired (1) or not (0) |
| PASSW_IN_GRACE |
NUMBER |
Indicates whether the account is in grace (1) or not (0) |
| PASSW_LOCKED |
NUMBER |
Indicates whether the account is locked (1) or not (0) |
| PASSW_LOCK_UNLIM |
NUMBER |
Indicates whether the account is locked for an unlimited time (1) or not (0) |
| FAILED_LOGINS |
NUMBER |
The number of failed login attempts. The count is not cumulative; it is reset upon successful logon to the account |
| EXPIRATION_AFTER_GRACE |
TIMESTAMP(3) |
The expiration time after grace |
| PASSW_LOCK_TIME |
TIMESTAMP(3) |
The time the account was locked out |
| CON_ID |
NUMBER |
The ID of the container where the failed login occurred. For users that are not common users, the CON_IDis the PDB ID where the failed login attempt occurred. For common users, the CON_ID is 0. The login attempts that occurred on a PDB are not displayed when you query V$RO_USER_ACCOUNT from another PDB. You only see the failed login attempts of any users (that are not common users) if those failed login attempts occurred on the same PDB from which you are queryingV$RO_USER_ACCOUNT. The failed login attempts of common users (and of the SYS user) are only displayed when V$RO_USER_ACCOUNT is queried from the root of a CDB, not when it is queried from a PDB. In a non-CDB, the value is always 0. |
2 实验
2.1 主库创建用户，使用默认PROFILE
默认PROFILE密码错误登录次数为10次，超过10次密码错误则锁定该用户。
SQL> create user lei identified by lei;
User created.
SQL> grant resource,connect to lei;
Grant succeeded.2.2 备库查看用户信息并用错误密码尝试连接
SQL> select s.username,t.userid,t.PASSW_LOCKED,t.FAILED_LOGINS
from V$RO_USER_ACCOUNT t,dba_users s
where t.userid=s.user_id; 2 3
no rows selected--错误密码尝试连接
[oracle@12cdg-p ~]$ sqlplus lei/cndba@sihong
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 16 10:59:39 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied--再次查看是否有错误信息
SQL> select s.username,t.userid,t.PASSW_LOCKED,t.FAILED_LOGINS
from V$RO_USER_ACCOUNT t,dba_users s
where t.userid=s.user_id;
USERNAME USERID PASSW_LOCKED FAILED_LOGINS
--------------- ---------- ------------ -------------
LEI 103 0-表示没有锁定 1 --失败登录次数--连续登录错误10次
SQL> select s.username,t.userid,t.PASSW_LOCKED,t.FAILED_LOGINS
from V$RO_USER_ACCOUNT t,dba_users s
where t.userid=s.user_id; 2 3
USERNAME USERID PASSW_LOCKED FAILED_LOGINS
--------------- ---------- ------------ -------------
LEI 103 1 --已锁定 10 --失败登录次数10，达到了锁定的条件--再次登录，则提示用户锁定
[oracle@www.cndba.cn ~]$ sqlplus lei/lei@sihong --哪怕是正确的密码
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 16 10:58:06 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-28000: the account is locked2.3 主库使用该用户连接
SQL> conn lei/lei@sihong
Connected.正常连接，不影响主库用户的使用。
2.4 解锁该用户-备库执行即可
SQL> alter user lei account unlock;
User altered.--查看用户信息
SQL> select s.username,t.userid,t.PASSW_LOCKED,t.FAILED_LOGINS
from V$RO_USER_ACCOUNT t,dba_users s
where t.userid=s.user_id; 2 3
no rows selected该用户的登录信息已经清空了。
--尝试登录
[oracle@www.cndba.cn ~]$ sqlplus lei/lei@sihong
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 16 11:07:59 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Aug 16 2017 11:05:11 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>登录成功。
版权声明：本文为博主原创文章，未经博主允许不得转载。
Active Data Guard Enhanced Security
