首页 » ORACLE 9i-23ai » Oracle 12c New Feature: Last Login Time for Non-Sys Users

Oracle 12c New Feature: Last Login Time for Non-Sys Users

  • 2016/12/17
  • ORACLE 9i-23ai
  • 1,801 views
  • Oracle 12c New Feature: Last Login Time for Non-Sys Users已关闭评论

对于操作系统的SHELL登录通常都会打印该用户的上一次登录时间, 这个安全特性在12c中同样在使用sqlplus登录时引入默认显示在banner中, 不同的是oracle不显示sys或操作系统认证的登录, 其实在之前的版本中对于这类用户的登录是都强置记录在Adump的审计文件中的, 对于Windows环境会记录在系统日志中. 对于非sqlplus的登录不显示同样也会记录用户上次的登录信息在表中, 很多年前记的我遇到个需求:找出数据库中上时间不登录的用户? 之前如果没有审计还要用LOGON触发器记录, 现在在12c方便了, 这里我演示一下该特性.

[oracle@anbob ~]$ sqlplus c##anbob/oracle
SQL*Plus: Release 12.2.0.0.0 Beta on Fri Dec 16 22:39:32 2016
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Last Successful login time: Fri Dec 16 2016 22:38:30 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

TIP:
从上面的加亮提示的部分就是新特性增加的提示信息. 这些信息是记录在user$表的SPARE6列.

SQL> @desc dba_users
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      USERNAME                        NOT NULL VARCHAR2(128)
    2      USER_ID                         NOT NULL NUMBER
    3      PASSWORD                                 VARCHAR2(4000)
    4      ACCOUNT_STATUS                  NOT NULL VARCHAR2(32)
    5      LOCK_DATE                                DATE
    6      EXPIRY_DATE                              DATE
    7      DEFAULT_TABLESPACE              NOT NULL VARCHAR2(30)
    8      TEMPORARY_TABLESPACE            NOT NULL VARCHAR2(30)
    9      LOCAL_TEMP_TABLESPACE                    VARCHAR2(30)
   10      CREATED                         NOT NULL DATE
   11      PROFILE                         NOT NULL VARCHAR2(128)
   12      INITIAL_RSRC_CONSUMER_GROUP              VARCHAR2(128)
   13      EXTERNAL_NAME                            VARCHAR2(4000)
   14      PASSWORD_VERSIONS                        VARCHAR2(12)
   15      EDITIONS_ENABLED                         VARCHAR2(1)
   16      AUTHENTICATION_TYPE                      VARCHAR2(8)
   17      PROXY_ONLY_CONNECT                       VARCHAR2(1)
   18      COMMON                                   VARCHAR2(3)
   19      LAST_LOGIN                               TIMESTAMP(9) WITH TIME ZONE
   20      ORACLE_MAINTAINED                        VARCHAR2(1)
   21      INHERITED                                VARCHAR2(3)
   22      DEFAULT_COLLATION                        VARCHAR2(100)
   23      IMPLICIT                                 VARCHAR2(3)

SQL> select last_login from dba_users where username='C##ANBOB';

LAST_LOGIN
---------------------------------------------------------------------------
16-DEC-16 10.38.30.000000000 PM +08:00

对于PDB的用户:

[oracle@anbob ~]$ sqlplus anbob/anbob

SQL*Plus: Release 12.2.0.0.0 Beta on Sat Dec 17 16:44:36 2016
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Last Successful login time: Fri Dec 02 2016 15:37:04 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

USERNAME             INST_NAME            HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
ANBOB                PDBORCL-orcl         anbob                     52    37497    12.2.0.0.1 20161217 5226       56    5224            00000000BE29EA28 00000000BFCCAF08

SQL>

对于/ SYSDBA 或其它OS认证:

[oracle@anbob ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.0.0 Beta on Sat Dec 17 16:45:34 2016
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
-- OR

[oracle@anbob ~]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 12.2.0.0.0 Beta on Sat Dec 17 16:45:59 2016
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

-- OR

SQL> create user weejar identified by weejar;
User created.
SQL> grant sysoper to weejar;
Grant succeeded.
SQL> grant create session to weejar;
Grant succeeded.

[oracle@anbob ~]$ sqlplus weejar/weejar as sysoper

SQL*Plus: Release 12.2.0.0.0 Beta on Sat Dec 17 17:01:32 2016
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>

SQL> select last_login from dba_users where username='WEEJAR';
LAST_LOGIN
----------------------------
--NONE

SQL> @desc v$pwfile_users
Name Null? Type
------------------------------- -------- ----------------------------
1 USERNAME VARCHAR2(128)
2 SYSDBA VARCHAR2(5)
3 SYSOPER VARCHAR2(5)
4 SYSASM VARCHAR2(5)
5 SYSBACKUP VARCHAR2(5)
6 SYSDG VARCHAR2(5)
7 SYSKM VARCHAR2(5)
8 ACCOUNT_STATUS VARCHAR2(30)
9 PROFILE VARCHAR2(128)
10 LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
11 LOCK_DATE DATE
12 EXPIRY_DATE DATE
13 EXTERNAL_NAME VARCHAR2(1024)
14 AUTHENTICATION_TYPE VARCHAR2(8)
15 COMMON VARCHAR2(3)
16 CON_ID NUMBER

SQL> select username,LAST_LOGIN,SYSDBA,SYSOPER from v$pwfile_users;

USERNAME LAST_LOGIN SYSDB SYSOP
------------------------------ ------------- ----- -----
SYS TRUE TRUE
WEEJAR FALSE TRUE

tip:
对于SYS 或sysoper这类操作认识的用户登录时不会显示.

登录时sqlplus 的-nologintime选项可以禁掉显示上次的登录时间.

SQL> select last_login from dba_users where username='C##ANBOB';

LAST_LOGIN
---------------------------------------------------------------------------
17-DEC-16 04.30.58.000000000 PM +08:00

[oracle@anbob ~]$ sqlplus -nologintime c##anbob/oracle
SQL*Plus: Release 12.2.0.0.0 Beta on Sat Dec 17 16:49:08 2016
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  select last_login from dba_users where username='C##ANBOB';

LAST_LOGIN
---------------------------------------------------------------------------
17-DEC-16 04.49.08.000000000 PM +08:00

TIP:
虽然使用nologintime可以关掉登录时的显示,但是同样还是会更新user$表里的登录时间. 后续版本(21c or newer 19c RU)中可能会引入_disable_last_successful_login_time 参数来禁用该特性，因为存在一些连接风暴导致library cache lock，p3值可以取到namespace为127，在等待Last_Successful_Logon_Time.

SQL> select KGLSTDSC from  X$KGLST where indx=127;

KGLSTDSC
----------------------------------------------------------------
Last_Successful_Logon_Time

Summary:
last login time特性是通过sqlplus时才显示,数据来自user$表, 不显示sys和其它通过操作组OS认证的用户, 可以使用-nologintime禁掉登录时的提示login信息,但是对于NONE-SYS用记同样还是会更新数据字典表.

— update 2021/08/16 —
如果在login 时hang或 libaray cache lock ,SQL如下
update user$ set spare6=DECODE(to_char(:2, ‘YYYY-MM-DD’), ‘0000-00-00’, to_date(NULL), :2) where user#=:1

user$.spare6对应的就是last_login , ORACLE已经对12c和19c推出了bug 补丁用入引入禁用LSLT, 需要安装Patch 32164034 才会引入_disable_last_successful_login_time 。

20c没有，21c base版本已经引入

SQL> @i

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS                  CDB$ROOT-oracle21c   oel7db1                     1 1     54741    21.0.0.0.0 20210821 11143      43    11000           0000000077741858 00000000775910E0


SQL> @pd login_time
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
       802   322 _disable_last_successful_login_time                      FALSE                          Disable writing Last Successful Login Time to USER$
打赏

, , ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：