Oracle如何查看當(dāng)前賬號的相關(guān)信息總結(jié)
本文轉(zhuǎn)載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者。轉(zhuǎn)載本文請聯(lián)系DBA閑思雜想錄公眾號。
關(guān)于Oracle數(shù)據(jù)庫的賬號，我們在維護(hù)數(shù)據(jù)庫的時候，偶爾可能需要獲取一些特殊信息。例如，賬號的創(chuàng)建時間、賬號的狀態(tài)、賬號的鎖定時間.....。正常情況下，我們可以通過DBA_USERS獲取大部分相關(guān)信息。但是有一些特殊信息，還必須通過不常用底層基表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)
其實我們經(jīng)常使用的DBA_USERS是同義詞，對應(yīng)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
通過上面的視圖定義，我們可以知道，大部分?jǐn)?shù)據(jù)來自于底層基表sys.user$。關(guān)于表sys.user$的結(jié)構(gòu)如下，我們可以從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
其中，我們可以獲取一下關(guān)鍵字段信息，具體如下
- NAME 用戶（User）或角色（Role）的名字
- TYPE# 0表示Role，1表示User
- CTIME 用戶的創(chuàng)建時間
- PTIME 密碼最后一次修改時間
- EXPTIME 密碼過期的時間
- LTIME 賬號最后一次鎖定的時間
- LCOUNT 用戶登錄失敗次數(shù)。
下面我們簡單測試驗證一下，
- 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>
其中g(shù)et_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');
另外，我們來測試一下賬號登錄失敗次數(shù)，在實驗前先解鎖賬號，用錯誤的賬號密碼嘗試登錄數(shù)據(jù)庫，你會發(fā)現(xiàn)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字段的值是一直累加到超過閾值鎖定呢?還是中間會清零呢?什么情況下會清零呢?如果你使用正確的密碼成功登錄數(shù)據(jù)庫后，你會發(fā)現(xiàn)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.
也就是說，只要你在鎖定之前，一旦成功登錄之后，該計數(shù)會被清零。在有些版本中，由于Bug，也會出現(xiàn)LCOUNT沒有正確反映登錄失敗次數(shù)的情況，例如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/
