Last Successful Login Time Oracle 12c: Part IV

In this concluding post of 4-part series on LSLT(Last Successful Login Time), I will cover some of the details related to LSLT implementation for Real Application Users.

Oracle 12c introduced a new feature called "Real Application Security" to better manage Application users, when they interact with the backend. One could create Application users, who can create lightweight application sessions to the database through the middle tier or create heavyweight database sessions by connecting to the database directly [Refer to Database Real Application Security Administrator's and Developer's Guide for further information).

Simple Example : In the following example, I am creating two application users, one a direct login application user and other a regular non-direct login application user. Once created, try to establish lightweight application and heavyweight database sessions

conn /as sysdba
exec xs_principal.create_user('appUser1');
exec xs_principal.create_user('appUser2');
exec xs_principal.set_password('appUser2','appPass2');
declare
  sessionid raw(16);
begin
  sys.dbms_xs_sessions.create_session('appUser1', sessionid);
  sys.dbms_xs_sessions.attach_session(sessionid);
  sys.dbms_xs_sessions.detach_session;
end;
/
SQL> conn appUser2/appPass2
Connected.

Dictionary View to Query LSLT : Unlike DBA_USERS, the dictionary view corresponding to RAS users DBA_XS_USERS does not have any column to display Last Successful Login Time field. At present, XS$PRIN (The sort of USER$ for RAS users) does have a column LSLOGONTIME, but it does not look like it is being populated in case of RAS user logons, as is evident from the following query.

conn /as sysdba

SQL> select 1 from XS$PRIN where PRIN# in (select id from XS$OBJ where name in ('APPUSER1','APPUSER2')) and LSLOGONTIME is NULL;
         1
----------
         1
         1

RAS was enhanced in 12.1.0.2 to support user password policy management, such as account locking, password aging, and expiry, password complexity verification, and password history management for direct login Real Application users. However, LSLT support was not included. Perhaps, in future releases, Oracle may add this feature. 

After Thought on LSLT for RAS users : Now that it is confirmed that LSLT is not recorded as part of XS$PRIN, let's establish another connection for direct login RAS user using SQL*Plus

%% sqlplus appUser2/appPass2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 12 06:27:30 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Oct 12 2015 06:23:42 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "APPUSER2"

As you can see, we are seeing an LSLT printed on the screen, though not necessarily those of RAS user !! So the natural question is, if XS$PRIN is not recording the LSLT, from where we are populating it ?? The answer is, all RAS users' (Application users) sessions gets treated internally as that of XS$NULL (DB User) session. So when the login happens, we are pulling the SPARE6 from USER$ of XS$NULL user instead of LSLOGONTIME from XS$PRIN for APPUSER2, resulting into an incorrect display of LSLT on SQL*Plus invocation. This can be confirmed from a LSLT query against DBA_USERS for XS$NULL DB user.

conn /as sysdba
SQL> select last_login from dba_users where username='XS$NULL';

LAST_LOGIN
-----------------------------------------------
12-OCT-15 06.27.30.000000000 AM -07:00

What this also means is, if there are two direct login Application users, SALESAPPUSER and HRAPPUSER, both of them will incorrectly be updating LSLT for XS$NULL user in USER$, but nothing in XS$PRIN. So it would suffice to say that LSLT is not supported (yet) for Real Application Security Direct Login Users, as on 12.1.0.2.

Further details on Last Successful Login Time

• Part I (Introduction/Usecase in Production Environment)
• Part II (Known Limitations/Workaround)
• Part III (Oracle Multitenant/Common Users)