Oracle Database 12c New Feature: Last Login Time

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]

Stefan Oehrli Post author9. September 2013 at 15:21

I did my tests on a non container DB. Therefor the user KING has con_id=0.

[cc lang=”sql”]
SQL> show parameter pluggable

NAME TYPE VALUE
———————————— ———– ——————————
enable_pluggable_database boolean FALSE
[/cc]

I now did as well a test with a container database and got the same results.
[cc lang=”sql”]
SQL> show parameter pluggable

NAME TYPE VALUE
———————————— ———– ——————————
enable_pluggable_database boolean TRUE
[/cc]

First create a local user KING_PDB in container SEC
[cc lang=”sql”]
SQL> ALTER SESSION SET CONTAINER=sec;

Session altered.

SQL> CREATE USER king_pdb IDENTIFIED BY kong CONTAINER=CURRENT;

User created.

SQL> GRANT CREATE SESSION TO king_pdb;

Grant succeeded.

SQL> GRANT sysbackup TO king_pdb;

Grant succeeded.

SQL> GRANT SELECT ON dba_users TO king_pdb;

Grant succeeded.

[/cc]

Login as SYSBACKUP and see that USER$ / DBA_USERS has not been updated.
[cc lang=”sql”]
oracle@urania:~/ [TCDB12] sqlplus king_pdb/kong@SEC AS sysbackup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 15:11:44 2013

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_PDB’;

USERNAME LAST_LOGIN
————— ————————-
KING_PDB

SQL> SHOW USER
USER is “SYSBACKUP”
SQL> SELECT * FROM v$pwfile_users WHERE username=’KING_PDB’;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
————— —– —– —– —– —– —– ———-
KING_PDB FALSE FALSE FALSE TRUE FALSE FALSE 3
[/cc]

When login as local user KING_PDB without SYSBACKUP the login time is updated.
[cc lang=”sql”]
oracle@urania:~/ [TCDB12] sqlplus king_pdb/kong@SEC

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 15:12:41 2013

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_PDB’;

USERNAME LAST_LOGIN
————— ————————-
KING_PDB 09.09.2013 15:12:41
[/cc]

What exactly was your test case? By the way I got the same results when creating a common user C##KING.

Cheers
Stefan

3 thoughts on “Oracle Database 12c New Feature: Last Login Time”

Suresh 9. September 2013 at 14:06

Nice post, but I have opposite results.

Is container databases are true in your case? If so where is KING created? I can see its in con_id =0 means king user created in cdb$root or no cdb is enabled

Just check are you getting same results whilst in CDB enabled databases?

Suresh.
http://www.oracle-info.com
Stefan Oehrli Post author9. September 2013 at 15:21

Hi

I did my tests on a non container DB. Therefor the user KING has con_id=0.

[cc lang=”sql”]
SQL> show parameter pluggable

NAME TYPE VALUE
———————————— ———– ——————————
enable_pluggable_database boolean FALSE
[/cc]

I now did as well a test with a container database and got the same results.
[cc lang=”sql”]
SQL> show parameter pluggable

NAME TYPE VALUE
———————————— ———– ——————————
enable_pluggable_database boolean TRUE
[/cc]

First create a local user KING_PDB in container SEC
[cc lang=”sql”]
SQL> ALTER SESSION SET CONTAINER=sec;

Session altered.

SQL> CREATE USER king_pdb IDENTIFIED BY kong CONTAINER=CURRENT;

User created.

SQL> GRANT CREATE SESSION TO king_pdb;

Grant succeeded.

SQL> GRANT sysbackup TO king_pdb;

Grant succeeded.

SQL> GRANT SELECT ON dba_users TO king_pdb;

Grant succeeded.

[/cc]

Login as SYSBACKUP and see that USER$ / DBA_USERS has not been updated.
[cc lang=”sql”]
oracle@urania:~/ [TCDB12] sqlplus king_pdb/kong@SEC AS sysbackup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 15:11:44 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_PDB’;

USERNAME LAST_LOGIN
————— ————————-
KING_PDB

SQL> SHOW USER
USER is “SYSBACKUP”
SQL> SELECT * FROM v$pwfile_users WHERE username=’KING_PDB’;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
————— —– —– —– —– —– —– ———-
KING_PDB FALSE FALSE FALSE TRUE FALSE FALSE 3
[/cc]

When login as local user KING_PDB without SYSBACKUP the login time is updated.
[cc lang=”sql”]
oracle@urania:~/ [TCDB12] sqlplus king_pdb/kong@SEC

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 15:12:41 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_PDB’;

USERNAME LAST_LOGIN
————— ————————-
KING_PDB 09.09.2013 15:12:41
[/cc]

What exactly was your test case? By the way I got the same results when creating a common user C##KING.

Cheers
Stefan
SureshSuresh 10. September 2013 at 09:23

Hi,

Exactly the same case what you tried above in an container enabled database. Somehow the last login for sysdba login is not refreshing with empty. The time showing was old though, so it appears this will work, Thanks for rework, I will test and resend you the output.

-Suresh

Comments are closed.

OraDBA

Just another Site about Oracle, Database Security, Linux, Mac OS X and more

Oracle Database 12c New Feature: Last Login Time

A simple example

A little less simple example

Conclusion

References

3 thoughts on “Oracle Database 12c New Feature: Last Login Time”