oracle锁住用户有什么用,oracle 用户user锁定lock如何知道是什么原因导致的

最新推荐文章于 2023-09-25 15:02:08 发布

no莫

最新推荐文章于 2023-09-25 15:02:08 发布

阅读量810

点赞数

文章标签： oracle锁住用户有什么用

问题

数据库用户被锁了，如何查看什么原因导致被锁的呢

结论

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 version

SQL> 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

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

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即账期何时会过期，此列一直有数据，只要用户创建