As Markus Flechtner has already mentioned in his blog, Oracle has started to record the last login time. It is a small but very useful 12c security feature and operates independently of the database audit. Nevertheless, there are some restrictions. But let’s start at the beginning…
A simple example
Ok, lets try to connect as user scott.
oracle@urania:~/ [TDB12] sqlplus scott/tiger SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 21:59:19 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Last Successful login time: Tue Aug 06 2013 07:29:29 +02:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options
The highlighted line above show the last login information. The information itself is stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.
SQL> col username for a15 SQL> col last_login for a25 SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS'; Session altered. SQL> select username,last_login from dba_users where username='SCOTT'; USERNAME LAST_LOGIN --------------- ------------------------- SCOTT 12.08.2013 21:59:19 col name for a15 alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'; SQL> select name,spare6 from user$ where name='SCOTT'; NAME SPARE6 --------------- ------------------- SCOTT 12.08.2013 19:59:19
The display of the last login information can also be disabled with -nologintime.
oracle@urania:~/ [TDB12] sqlplus -nologintime scott/tiger SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 22:24:04 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options
Nevertheless the login time is still being recorded. Visible through the identical timestamp Mon Aug 12 22:24:04 2013 above and below
SQL> col username for a15 SQL> col last_login for a25 SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS'; Session altered. SQL> select username,last_login from dba_users where username='SCOTT'; USERNAME LAST_LOGIN --------------- ------------------------- SCOTT 12.08.2013 22:24:04
Ok, whats about SYSDBA and other password file users?
A little less simple example
Let’s create a test user with required privileges. That is CREATE SESSION, SYSBACKUP and SELECT on DBA_USERS
SQL> create user king identified by kong; User created. SQL> grant create session to king; Grant succeeded. SQL> grant sysbackup to king; Grant succeeded. SQL> grant select on dba_users to king; Grant succeeded.
First we initiated an SQL*Plus session as SYSBACKUP
oracle@urania:~/ [TDB12] sqlplus king/kong as sysbackup SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 22:35:36 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options SQL> col username for a15 SQL> col last_login for a25 SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS'; SQL> select username,last_login from dba_users where username='KING'; USERNAME LAST_LOGIN --------------- ------------------------- KING
As seen above, no logon time is recorded. This is because the user KING has not been authenticated by a password but rather by OS authentication and the corresponding OS group. The same applies if the password file is used for authentication.
SQL> show user USER is "SYSBACKUP" SQL> select * from v$pwfile_users where username='KING'; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID --------------- ----- ----- ----- ----- ----- ----- ---------- KING FALSE FALSE FALSE TRUE FALSE FALSE 0
But again if we connect as regular user with password authentication the logon time will be recorded. In this case nothing will be displayed because it’s the first time the user KING is logging into the database.
oracle@urania:~/ [TDB12] sqlplus king/kong SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 22:46:28 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options SQL> col username for a15 SQL> col last_login for a25 SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS'; Session altered. SQL> select username,last_login from dba_users where username='KING'; USERNAME LAST_LOGIN --------------- ------------------------- KING 12.08.2013 22:46:28 SQL> show user USER is "KING"
Conclusion
As stated above, this is a small but nice security feature. It does record logon and connect from any regular user. For a DBA , it is easy to check whether a user has logged in and how long it’s been.
SQL> select username,last_login from dba_users where last_login is not null order by last_login; USERNAME LAST_LOGIN --------------- ------------------------- HR 05.08.2013 09:04:06 SYSTEM 05.08.2013 14:52:12 TEST 12.08.2013 21:14:45 SCOTT 12.08.2013 22:24:04 KING 12.08.2013 22:46:28
On the other hand, there are some small drawbacks.
- No login time recording for administrative users respectively password file users
- Login time is only displayed when logged in by SQL*Plus
- -nologintime just switch of display not recording of login time
Since this feature is intended primarily for interactive use, the limitations are understandable to a certain degree. Especially for SYSDBA, SYSDG and SYSBACKUP the last login time would be flooded by a bunch of internal connections by RMAN or dataguard. To ensure the traceability, it is mandatory to use database audit including audit of SYS operation.
References
Links related to this post, password file users, security and more
- Oracle® Database New Features Guide 12c Release 1 (12.1) Last Login Time Information
- SQL*Plus® User’s Guide and Reference Release 12.1 No Login Time Option
- SYSDBA and SYSOPER Privileges in Oracle [50507.1]
- Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues [233223.1]
- Why Can I Login AS SYSDBA With any Username and Password? [242258.1]
- All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [207959.1]