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