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

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

[[email protected] ~]$ 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的用户:

[[email protected] ~]$ 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认证:

[[email protected] ~]$ 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

[[email protected] ~]$ 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.

[[email protected] ~]$ 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

[[email protected] ~]$ 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用记同样还是会更新数据字典表.

  • 版权声明: 本文源自互联网, 于3个月前,由整理发表,共 4881字。
  • 原文链接:点此查看原文