ORACLE如何查看当前账号的相关信息总结
2021-06-10 15:27 潇湘隐者 阅读(1022) 评论(0) 编辑 收藏 举报关于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 DATEDEFAULT_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 NUMBERNAME 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 DATERESOURCE$ 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表示UserCTIME 用户的创建时间
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 0SQL> 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 0SQL> 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 0SQL>
其中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 1SQL>
那么这个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/
扫描上面二维码关注我
如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
