对于操作系统的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$