服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - Oracle如何查看当前账号的相关信息总结

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

2021-06-10 23:03DBA闲思杂想录潇湘隐者 Oracle

关于Oracle数据库的账号,我们在维护数据库的时候,偶尔可能需要获取一些特殊信息。

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

关于Oracle数据库的账号,我们在维护数据库的时候,偶尔可能需要获取一些特殊信息。例如,账号的创建时间、账号的状态、账号的锁定时间.....。正常情况下,我们可以通过DBA_USERS获取大部分相关信息。但是有一些特殊信息,还必须通过不常用底层基表sys.user$来获取。

  1. SQL> DESC DBA_USERS; 
  2.  Name                                      Null?    Type 
  3.  ----------------------------------------- -------- ---------------------------- 
  4.  USERNAME                                  NOT NULL VARCHAR2(30) 
  5.  USER_ID                                   NOT NULL NUMBER 
  6.  PASSWORD                                           VARCHAR2(30) 
  7.  ACCOUNT_STATUS                            NOT NULL VARCHAR2(32) 
  8.  LOCK_DATE                                          DATE 
  9.  EXPIRY_DATE                                        DATE 
  10.  DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30) 
  11.  TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30) 
  12.  CREATED                                   NOT NULL DATE 
  13.  PROFILE                                   NOT NULL VARCHAR2(30) 
  14.  INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30) 
  15.  EXTERNAL_NAME                                      VARCHAR2(4000) 

其实我们经常使用的DBA_USERS是同义词,对应SYS.DBA_USERS这个视图。如果你想查看SYS.DBA_USERS的定义,可以通过下面方式:

  1. --ORACLE 10g 
  2.  
  3. SQL>SELECT DBMS_METADATA.GET_DDL('VIEW''DBA_USERS''SYS'FROM DUAL; 
  4.  
  5.  
  6.  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_USERS" ( 
  7.   "USERNAME" 
  8. "USER_ID" 
  9. "PASSWORD" 
  10. "ACCOUNT_STATUS" 
  11. "LOCK_DATE" 
  12. "EXPIRY_DATE" 
  13. "DEFAULT_TABLESPACE" 
  14. "TEMPORARY_TABLESPACE" 
  15. "CREATED" 
  16. "PROFILE" 
  17. "INITIAL_RSRC_CONSUMER_GROUP" 
  18. "EXTERNAL_NAME"AS  
  19.   select u.name, u.user#, u.password
  20.        m.status, 
  21.        decode(u.astatus, 4, u.ltime, 
  22.                          5, u.ltime, 
  23.                          6, u.ltime, 
  24.                          8, u.ltime, 
  25.                          9, u.ltime, 
  26.                          10, u.ltime, to_date(NULL)), 
  27.        decode(u.astatus, 
  28.               1, u.exptime, 
  29.               2, u.exptime, 
  30.               5, u.exptime, 
  31.               6, u.exptime, 
  32.               9, u.exptime, 
  33.               10, u.exptime, 
  34.               decode(u.ptime, '', to_date(NULL), 
  35.                 decode(pr.limit#, 2147483647, to_date(NULL), 
  36.                  decode(pr.limit#, 0, 
  37.                    decode(dp.limit#, 2147483647, to_date(NULL), u.ptime + 
  38.                      dp.limit#/86400), 
  39.                    u.ptime + pr.limit#/86400)))), 
  40.        dts.name, tts.name, u.ctime, p.name
  41.        nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'), 
  42.        u.ext_username 
  43.        from sys.user$ u left outer join sys.resource_group_mapping$ cgm 
  44.             on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and 
  45.                 cgm.value = u.name), 
  46.             sys.ts$ dts, sys.ts$ tts, sys.profname$ p, 
  47.             sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp 
  48.        where u.datats# = dts.ts# 
  49.        and u.resource$ = p.profile# 
  50.        and u.tempts# = tts.ts# 
  51.        and u.astatus = m.status# 
  52.        and u.type# = 1 
  53.        and u.resource$ = pr.profile# 
  54.        and dp.profile# = 0 
  55.        and dp.type#=1 
  56.        and dp.resource#=1 
  57.        and pr.type# = 1 
  58.        and pr.resource# = 1 

通过上面的视图定义,我们可以知道,大部分数据来自于底层基表sys.user$。关于表sys.user$的结构如下,我们可以从sql.bsq中可以得到sys.user$的定义。

  1. SQL> DESC sys.user
  2.  Name                                      Null?    Type 
  3.  ----------------------------------------- -------- ---------------------------- 
  4.  USER#                                     NOT NULL NUMBER 
  5.  NAME                                      NOT NULL VARCHAR2(30)  
  6.  TYPE#                                     NOT NULL NUMBER 
  7.  PASSWORD                                           VARCHAR2(30) 
  8.  DATATS#                                   NOT NULL NUMBER 
  9.  TEMPTS#                                   NOT NULL NUMBER 
  10.  CTIME                                     NOT NULL DATE 
  11.  PTIME                                              DATE 
  12.  EXPTIME                                            DATE 
  13.  LTIME                                              DATE 
  14.  RESOURCE$                                 NOT NULL NUMBER 
  15.  AUDIT$                                             VARCHAR2(38) 
  16.  DEFROLE                                   NOT NULL NUMBER 
  17.  DEFGRP#                                            NUMBER 
  18.  DEFGRP_SEQ#                                        NUMBER 
  19.  ASTATUS                                   NOT NULL NUMBER 
  20.  LCOUNT                                    NOT NULL NUMBER 
  21.  DEFSCHCLASS                                        VARCHAR2(30) 
  22.  EXT_USERNAME                                       VARCHAR2(4000) 
  23.  SPARE1                                             NUMBER 
  24.  SPARE2                                             NUMBER 
  25.  SPARE3                                             NUMBER 
  26.  SPARE4                                             VARCHAR2(1000) 
  27.  SPARE5                                             VARCHAR2(1000) 
  28.  SPARE6                                             DATE 

其中,我们可以获取一下关键字段信息,具体如下

  1. NAME    用户(User)或角色(Role)的名字  
  2. TYPE#   0表示Role,1表示User 
  3. CTIME   用户的创建时间 
  4. PTIME   密码最后一次修改时间 
  5. EXPTIME     密码过期的时间 
  6. LTIME       账号最后一次锁定的时间 
  7. LCOUNT      用户登录失败次数。 

下面我们简单测试验证一下,

  1. SQL> CREATE USER TEST IDENTIFIED BY "Test#1232134$#3" DEFAULT TABLESPACE TBS_TEST_DATA TEMPORARY TABLESPACE  TEMP
  2.  
  3. User created. 
  4. SQL> GRANT CONNECT TO TEST; 
  5. SQL> @get_user_info.sql 
  6.  
  7. Session altered. 
  8.  
  9. Enter value for user_name: TEST 
  10. old   9: WHERE NAME=('&USER_NAME'
  11. new   9: WHERE NAME=('TEST'
  12.  
  13. NAME                                TYPE# CTIME               PTIME               EXPTIME             LTIME                   LCOUNT 
  14. ------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ---------- 
  15. TEST                                    1 2021-06-10 14:10:01 2021-06-10 14:10:01                                                  0 
  16.  
  17. SQL> ALTER USER TEST IDENTIFIED BY "kER124"
  18.  
  19. User altered. 
  20.  
  21. SQL> @get_user_info.sql 
  22.  
  23. Session altered. 
  24.  
  25. Enter value for user_name: TEST 
  26. old   9: WHERE NAME=('&USER_NAME'
  27. new   9: WHERE NAME=('TEST'
  28.  
  29. NAME                                TYPE# CTIME               PTIME               EXPTIME             LTIME                   LCOUNT 
  30. ------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ---------- 
  31. TEST                                    1 2021-06-10 14:10:01 2021-06-10 14:10:50                                                  0 
  32.  
  33. SQL> ALTER USER TEST ACCOUNT LOCK; 
  34.  
  35. User altered. 
  36.  
  37. SQL> @get_user_info.sql 
  38.  
  39. Session altered. 
  40.  
  41. Enter value for user_name: TEST 
  42. old   9: WHERE NAME=('&USER_NAME'
  43. new   9: WHERE NAME=('TEST'
  44.  
  45. NAME                                TYPE# CTIME               PTIME               EXPTIME             LTIME                   LCOUNT 
  46. ------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ---------- 
  47. TEST                                    1 2021-06-10 14:10:01 2021-06-10 14:10:50                     2021-06-10 14:11:27          0 
  48.  
  49. SQL>  

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

其中get_user_info.sql的脚本如下

  1. $ more get_user_info.sql  
  2. ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
  3. SELECT  NAME 
  4.       , TYPE# 
  5.       , CTIME 
  6.       , PTIME 
  7.       , EXPTIME 
  8.       , LTIME 
  9.       , LCOUNT 
  10. FROM user
  11. WHERE NAME=('&USER_NAME'); 

另外,我们来测试一下账号登录失败次数,在实验前先解锁账号,用错误的账号密码尝试登录数据库,你会发现LCOUNT就变成1了。

  1. SQL> @get_user_info.sql 
  2.  
  3. Session altered. 
  4.  
  5. Enter value for user_name: TEST 
  6. old   9: WHERE NAME=('&USER_NAME'
  7. new   9: WHERE NAME=('TEST'
  8.  
  9. NAME                                TYPE# CTIME               PTIME               EXPTIME             LTIME                   LCOUNT 
  10. ------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ---------- 
  11. TEST                                    1 2021-06-10 14:10:01 2021-06-10 14:10:50                     2021-06-10 14:11:27          1 
  12.  
  13. SQL>  

那么这个LCOUNT字段的值是一直累加到超过阈值锁定呢?还是中间会清零呢?什么情况下会清零呢?如果你使用正确的密码成功登录数据库后,你会发现LCOUNT的值就清零了。如下截图所示:

  1. $ sqlplus /nolog 
  2.  
  3. SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 10 14:30:41 2021 
  4.  
  5. Copyright (c) 1982, 2007, Oracle.  All Rights Reserved. 
  6.  
  7. SQL> connect TEST 
  8. Enter password:  
  9. Connected. 

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

也就是说,只要你在锁定之前,一旦成功登录之后,该计数会被清零。在有些版本中,由于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/

原文链接:https://mp.weixin.qq.com/s/UE0z6PJ8usboFMmHpTBuOg

延伸 · 阅读

精彩推荐