oracle 使用者user鎖定lock如何知道是什麼原因導致的
問題
資料庫使用者被鎖了，如何檢視什麼原因導致被鎖的呢結論
1,dba_users是由底層表user$,profile$,profname$相關表構成，當然還有ts$2,dba_users與賬戶鎖定或過期相關的列有：created,account_status,lock_date,expiry_date,profile
3,具體如何分析到底是dba_profiles中的哪個引數為使用者鎖定的原因，要結合上述這些列的資料，以及dba_profiles相關記錄的含義
進行一一排除
4,查詢profile的含義方法為：在oracle sql rererence找create profile即可
或者從administrator guide找resource plan,也可以導航到create profile即可
5,profile說白了，就是控制資源如何使用的，具體細節大家可參考官方手冊
測試
--oracle versionSQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
--使用者字典表
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)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SQL> create user test_user identified by system account unlock;
User created.
可見預設建立使用者使用default profile
SQL> select username,password,account_status,lock_date,profile from dba_users where lower(username)='test_user';
USERNAME PASSWORD ACCOUNT_ST LOCK_DATE PROFILE
-------------------- ------------------------------ ---------- ------------ ------------------------------------------------------------
TEST_USER OPEN DEFAULT
看看default profile
關於profile的含義，自己可以查查官方手冊即知
SQL> select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------------------------- ---------------- --------------------------------------------------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 --注意下與password相關的記錄
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------------------------- ---------------- --------------------------------------------------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
測試下，連續多次用不正確密碼登陸，直到10次，賬戶鎖了
[oracle@seconary ~]$ sqlplus test_user/p1
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 16 03:54:36 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
--中間類似內容略
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@seconary ~]$ sqlplus test_user/p1
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 16 03:54:48 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-28000: the account is locked
account_status及lock_date有值了，表明賬戶渙定了，如果你現在想知道到底為何被鎖了，只能查詢底層的相關基表了
SQL> select username,password,account_status,lock_date,profile from dba_users where lower(username)='test_user';
USERNAME PASSWORD ACCOUNT_STATUS LOCK_DATE PROFILE
-------------------- -------------------- ------------------------------ ------------------- ------------------------------------------------------------
TEST_USER LOCKED(TIMED) 2015-11-16 03:54:43 DEFAULT
從底層定義的基表看，只有user$及profile$是我想分析所關聯的基表
SQL> set long 99999999
SQL> set pagesize 300
SQL> select view_name,text from dba_views where view_name='DBA_USERS';
VIEW_NAME TEXT
------------------------------------------------------------ --------------------------------------------------------------------------------
DBA_USERS select u.name, u.user#,
decode(u.password, 'GLOBAL', u.password,
'EXTERNAL', u.password,
NULL),
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,
decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL),
decode(bitand(u.spare1, 16),
16, 'Y',
'N'),
decode(u.password, 'GLOBAL', 'GLOBAL',
'EXTERNAL', 'EXTERNAL',
'PASSWORD')
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
先看下user$,只有列resource$是我們關注的列
create table user$ /* user table */
( user# number not null, /* user identifier number */
name varchar2("M_IDEN") not null, /* name of user */
/* 0 = role, 1 = user, 2 = adjunct schema, 3 = schema synonym */
type# number not null,
password varchar2("M_IDEN"), /* encrypted password */
datats# number not null, /* default tablespace for permanent objects */
tempts# number not null, /* default tablespace for temporary tables */
ctime date not null, /* user account creation time */
ptime date, /* password change time */
exptime date, /* actual password expiration time */
ltime date, /* time when account is locked */
resource$ number not null, /* resource profile# */
audit$ varchar2("S_OPFL"), /* user audit options */
defrole number not null, /* default role indicator: */
/* 0 = no roles, 1 = all roles granted, 2 = roles in defrole$ */
defgrp# number, /* default undo group */
defgrp_seq# number, /* global sequence number for the grp *
spare varchar2("M_IDEN"), /* reserved for future */
astatus number default 0 not null, /* status of the account */
/* 0x00 = 0 = Open */
/* 0x01 = 1 = Locked */
/* 0x02 = 2 = Expired */
/* 0x03 = 3 = Locked and Expired */
/* 0x10 = 16 = Password matches a default value */
lcount number default 0 not null, /* count of failed login attempts */
defschclass varchar2("M_IDEN"), /* initial consumer group */
ext_username varchar2("M_VCSZ"), /* external username */
/* also as base schema name for adjunct schemas */
spare1 number, /* used for schema level supp. logging: see ktscts.h */
spare2 number, /* used to store edition id for adjunct schemas */
spare3 number,
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date
)
cluster c_user#(user#)
而據上可知，user$的resource$是與表profile$的profile#關聯，所以還是要分析profile$表
VIEW_NAME TEXT
------------------------------------------------------------ --------------------------------------------------------------------------------
DBA_PROFILES select
n.name, m.name,--對應dba_profiles的resource_name
decode(u.type#, 0, 'KERNEL', 1, 'PASSWORD', 'INVALID'),
decode(u.limit#,
0, 'DEFAULT',
2147483647, decode(u.resource#,
4, decode(u.type#,
1, 'NULL', 'UNLIMITED'),
'UNLIMITED'),
decode(u.resource#,
4, decode(u.type#, 1, o.name, u.limit#),--對應dba_profiles的resource_type
decode(u.type#,
0, u.limit#,
decode(u.resource#,
1, trunc(u.limit#/86400, 4),
2, trunc(u.limit#/86400, 4),
5, trunc(u.limit#/86400, 4),
6, trunc(u.limit#/86400, 4),
u.limit#)))) --對應dba_profiles的limit
from sys.profile$ u, sys.profname$ n, sys.resource_map m, sys.obj$ o
where u.resource# = m.resource#
and u.type#=m.type#
and o.obj# (+) = u.limit#
and n.profile# = u.profile#
SQL> desc dba_profiles;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)
綜上分析下，你只要關注resource_type='password',並且limit為unlimited及null不用你管了（原因不用我了），所以只有我標註幾條記錄
SQL> select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------------------------- ---------------- --------------------------------------------------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 ---關注，經查，這是登陸的次數
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 --關注 --這是賬戶有效期的天數，即用同一個賬戶密碼登陸
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------------------------- ---------------- --------------------------------------------------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 ---關注，表明連續多次失敗登陸後，賬戶鎖定的天數，預設為1天
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 --關注 ，指定多少天，即如果你在指定天數之內不改密碼就會提示密碼失敗無法登陸
關注上述引數的含義，請見：
Oracle? Database SQL Language Reference
11g Release 2 (11.2)
Part Number E26088-03
之
create profile
可見expiry_date即賬期何時會過期，此列一直有資料，只要使用者建立
SQL> select username,password,account_status,expiry_date,lock_date,profile from dba_users;
USERNAME PASSWORD ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE PROFILE
-------------------- -------------------- ------------------------------ ------------------- ------------------- ------------------------------
SCOTT OPEN 2016-03-06 06:58:08 DEFAULT
TEST1 OPEN 2016-05-03 11:59:30 DEFAULT
TEST_USER LOCKED(TIMED) 2016-05-14 03:45:06 2015-11-16 03:54:43 DEFAULT
但是與password相關在dba_profiles有幾條記錄，我們知道是哪條記錄是導致使用者鎖定的原因呢
所以結合測試使用者建立的時間dba_users.created就可以排除password_life_time,password_lock_time也可以排除，因為它是賬戶被鎖定的表數，也就是說已發生在賬戶鎖定之後的動作了
這樣就只有failed_login_attempts和password_grace_time
我們經查官方手冊發現,password_grace_time一般和password_life_time搭配使用，即後者要小於前者，即如果在後者指定的天數不修改密碼，使用者就會過期不能登陸了，所以
只能是引數failed_login_attempts
SQL> select username,password,account_status,expiry_date,lock_date,profile from dba_users where lower(username)='test_user';
USERNAME PASSWORD ACCOUNT_STATUS EXPIRY_DATE LOCK_DATE PROFILE
-------------------- -------------------- ------------------------------ ------------------- ------------------- ------------------------------
TEST_USER LOCKED(TIMED) 2016-05-14 03:45:06 2015-11-16 03:54:43 DEFAULT
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1839740/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle使用者密碼被鎖定導致的故障Oracle密碼
- SSL證書與域名不匹配是什麼原因導致的？
- 為什麼網站提示SSL證書不受信任？是哪些原因導致的？網站
- Oracle LOCK內部機制及最佳實踐系列(二)模擬RI鎖定導致阻塞的場景，並分析v$lockOracle
- 市場大跌原因剖析：是什麼導致了加密貨幣近期的低迷狀態加密
- SSL證書出錯是怎麼回事？是由哪些原因導致的？
- MySQL空間暴漲150G導致鎖定，發生了什麼MySql
- MySQL next-key lock 加鎖範圍是什麼？MySql
- 什麼是死鎖？如何解決死鎖？
- oracle bug 6825287導致DX鎖等待Oracle
- 導致IP被封的原因
- 什麼是USER ITEM TPYE?
- 模擬RI鎖定導致阻塞的場景
- 合成遊戲是什麼模式，導致它這麼火爆呢遊戲模式
- oracle lock鎖_v$lock_轉Oracle
- Synchronized鎖的是什麼？synchronized
- java中lock介面是什麼Java
- Oracle資料庫導致效能問題的可能原因Oracle資料庫
- 導致InvocationTargetException的最常見原因Exception
- 來看看是什麼原因導致生產服上的系統CPU高的？
- 【分散式鎖的演化】什麼是鎖？分散式
- Oracle裡面的user被lock了Oracle
- 目標錯位是導致效果不佳的最終原因
- 11G 修改使用者密碼導致的row cache lock密碼
- 一次oracle行級鎖導致的問題Oracle
- 什麼是Godaddy?站長使用它的原因是什麼Go
- 網站seo排名穩定的法寶你知道是什麼？網站
- ORACLE 歸檔空間滿導致的enq: TX - row lock contentionOracleENQ
- 物件導向中類和物件的定義是什麼?物件
- 導致的汽車油耗升高的原因分析
- PLC程式故障是什麼原因？如何遠端維護？C程式
- 什麼是分散式鎖？分散式
- Python程式閃退的原因是什麼?如何解決?Python
- RESTful API的流行的原因是什麼？RESTAPI
- 你知道什麼是 GitHub Action 麼？Github
- 【mybatis-plus】什麼是樂觀鎖？如何實現“樂觀鎖”MyBatis
- 併發insert操作導致的dead lock
- mysql啟動不了是什麼原因MySql