关于oracle数据库的账号，我们在维护数据库的时候，偶尔可能需要获取一些特殊信息。例如，账号的创建时间、账号的状态、账号的锁定时间…..。正常情况下，我们可以通过dba_users获取大部分相关信息。但是有一些特殊信息，还必须通过不常用底层基表sys.user$来获取。
sql> desc dba_users;
name null? type
----------------------------------------- -------- ----------------------------
username not null varchar2(30)
user_id not null number
password varchar2(30)
account_status not null varchar2(32)
lock_date date
expiry_date date
default_tablespace not null varchar2(30)
temporary_tablespace not null varchar2(30)
created not null date
profile not null varchar2(30)
initial_rsrc_consumer_group varchar2(30)
external_name varchar2(4000)
其实我们经常使用的dba_users是同义词，对应sys.dba_users这个视图。如果你想查看sys.dba_users的定义，可以通过下面方式：
--oracle 10g
sql>select dbms_metadata.get_ddl('view', 'dba_users', 'sys') from dual;
create or replace force view "sys"."dba_users" (
"username"
, "user_id"
, "password"
, "account_status"
, "lock_date"
, "expiry_date"
, "default_tablespace"
, "temporary_tablespace"
, "created"
, "profile"
, "initial_rsrc_consumer_group"
, "external_name") as
select u.name, u.user#, u.password,
m.status,
decode(u.astatus, 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(null)),
decode(u.astatus,
1, u.exptime,
2, u.exptime,
5, u.exptime,
6, u.exptime,
9, u.exptime,
10, u.exptime,
decode(u.ptime, '', to_date(null),
decode(pr.limit#, 2147483647, to_date(null),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(null), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400)))),
dts.name, tts.name, u.ctime, p.name,
nvl(cgm.consumer_group, 'default_consumer_group'),
u.ext_username
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = 'oracle_user' and cgm.status = 'active' and
cgm.value = u.name),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
通过上面的视图定义，我们可以知道，大部分数据来自于底层基表sys.user$。关于表sys.user$的结构如下，我们可以从sql.bsq中可以看到sys.user$的定义。
sql> desc sys.user$
name null? type
----------------------------------------- -------- ----------------------------
user# not null number
name not null varchar2(30)
type# not null number
password varchar2(30)
datats# not null number
tempts# not null number
ctime not null date
ptime date
exptime date
ltime date
resource$ not null number
audit$ varchar2(38)
defrole not null number
defgrp# number
defgrp_seq# number
astatus not null number
lcount not null number
defschclass varchar2(30)
ext_username varchar2(4000)
spare1 number
spare2 number
spare3 number
spare4 varchar2(1000)
spare5 varchar2(1000)
spare6 date
其中，我们可以获取一下关键字段信息，具体如下
name 用户（user）或角色（role）的名字
type# 0表示role，1表示user
ctime 用户的创建时间
ptime 密码最后一次修改时间
exptime 密码过期的时间
ltime 账号最后一次锁定的时间
lcount 用户登录失败次数。
下面我们简单测试验证一下，
sql> create user test identified by "test#1232134$#3" default tablespace tbs_test_data temporary tablespace temp;
user created.
sql> grant connect to test;
sql> @get_user_info.sql
session altered.
enter value for user_name: test
old 9: where name=('&user_name')
new 9: where name=('test')
name type# ctime ptime exptime ltime lcount
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ----------
test 1 2021-06-10 14:10:01 2021-06-10 14:10:01 0
sql> alter user test identified by "ker124";
user altered.
sql> @get_user_info.sql
session altered.
enter value for user_name: test
old 9: where name=('&user_name')
new 9: where name=('test')
name type# ctime ptime exptime ltime lcount
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ----------
test 1 2021-06-10 14:10:01 2021-06-10 14:10:50 0
sql> alter user test account lock;
user altered.
sql> @get_user_info.sql
session altered.
enter value for user_name: test
old 9: where name=('&user_name')
new 9: where name=('test')
name type# ctime ptime exptime ltime lcount
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ----------
test 1 2021-06-10 14:10:01 2021-06-10 14:10:50 2021-06-10 14:11:27 0
sql>
其中get_user_info.sql的脚本如下
$ more get_user_info.sql
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select name
, type#
, ctime
, ptime
, exptime
, ltime
, lcount
from user$
where name=('&user_name');
另外，我们来测试一下账号登录失败次数，在实验前先解锁账号，用错误的账号密码尝试登录数据库，你会发现lcount就变成1了。
sql> @get_user_info.sql
session altered.
enter value for user_name: test
old 9: where name=('&user_name')
new 9: where name=('test')
name type# ctime ptime exptime ltime lcount
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ----------
test 1 2021-06-10 14:10:01 2021-06-10 14:10:50 2021-06-10 14:11:27 1
sql>
那么这个lcount字段的值是一直累加到超过阈值锁定呢？还是中间会清零呢？什么情况下会清零呢？ 如果你使用正确的密码成功登录数据库后，你会发现lcount的值就清零了。如下截图所示：
$ sqlplus /nolog
sql*plus: release 10.2.0.4.0 - production on thu jun 10 14:30:41 2021
copyright (c) 1982, 2007, oracle. all rights reserved.
sql> connect test
enter password:
connected.
也就是说，只要你在锁定之前，一旦成功登录之后，该计数会被清零。在有些版本中，由于bug，也会出现lcount没有正确反映登录失败次数的情况，例如lcount neither reset on correct login nor incremented after incorrect login thru jdbc (doc id 2675398.1)中记录了这样的bug。另外，oracle 12c 后新增了一个功能，它会记录用户的最后一次登录时间：spare6字段记录用户的最后一次登录时间
参考资料：
https://www.eygle.com/archives/2009/07/profile_failed_login_attempts.html
https://dbaora.com/sys-user-table-in-oracle-last-password-change-time-last-locked-last-expired-creation-time-failed-logon/
lcount neither reset on correct login nor incremented after incorrect login thru jdbc (doc id 2675398.1)
https://bijoos.com/oraclenotes/2013/153/