Oracle如何查看当前账号的相关信息总结

[[405038]]

本文转载自微信公众号「DBA闲思杂想录」，作者潇湘隐者。转载本文请联系DBA闲思杂想录公众号。  

关于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)中记录了这样的案例。另外，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/