欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

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

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