Last Successful Login Time Oracle 12c: Part III

In my earlier posts [Part I Part II], I had described LSLT(Last Successful Login Time) at length and explained how the newly introduced feature in 12c can be used to weed out inactive user accounts in a production environment. Today, I am going to explain how does it behaves for local and common users, when connected to a CDB environment aka Oracle Multitenant. If you are not already aware of(must be sleeping to do so), Oracle multi-tenant supports two types of users, depending on the container types 

1) Local User : These are same as the customer created users in pre-12c era. Since such users exists only within a PDB (Pluggable Database) and CDB$ROOT container can't have LOCAL users, whatever has been discussed in Part I and Part II continues to hold good for PDB local users.

2) Common User : Since the common user (Either an Oracle supplied one or a customer created one with "C##" prefix) exists as a valid user across all the containers, a common user can have as many as 1 (CDB$ROOT) + 252 (PDBs) = 253 LSLTs across the CDB. Here is how it behaves, depending on container, it connects to.

• A) Connection to ROOT : For common users' successful connection to CDB$ROOT, only ROOT's user$ has its LSLT updated. LSLT(s), if any, present inside PDBs' user$ are left untouched.
• B) Connection to PDB : For common users' successful connection to PDB, Last Successful Login Time is updated in two places i> CDB$Root as well as ii> The PDB to which connection is being attempted. The LSLT, if any, present in other PDBs is left untouched.

What this essentially means is the followings

• C) DBA_USERS at CDB$ROOT would show the LAST_LOGIN as the time when the common user last connected to any of the PDB's, including CDB$ROOT.
• D) DBA_USERS inside a PDB would show the LAST_LOGIN as the time when the common user last connected to that PDB. This time might be different from the LAST_LOGIN in the CDB$ROOT or in other PDBs, as that common user could have connected to any other PDB in between the last login to this PDB and now.

3) Which containers' LSLT gets displayed at the time of logon ?? For connections to ROOT or to the PDBs, the LSLT displayed during SQL*Plus invocation, is always the one fetched from CDB$ROOT, as LSLT is associated to a user account instead of containers, he or she should always see his LAST SUCCESSFUL LOGIN ATTEMPT, irrespective of which containers it happened against.

conn /as sysdba

create user c##a identified by a;

grant create session to c##a container=all;

conn c##a/a@rootorcl

conn /as sysdba

SQL> show con_name

CON_NAME

-------------

CDB$ROOT

SQL> select last_login from dba_users where username='C##A';

LAST_LOGIN
-----------------------------------------------

12-OCT-15 03.38.00.000000000 PM +05:30

alter session set container=pdborcl;

SQL> show con_name

CON_NAME

-------------

PDBORCL

SQL> select 1 from dba_users where username='C##A' and last_login is NULL;

         1

----------

         1

The LSLT inside PDB's USER$ is expectedly showing up as NULL, as the common user has not logged into the PDB at all. However, when he or she tries to login to the PDB, it does show the LSLT, as was recorded in CDB$ROOT's USER$.

%% sqlplus c##a/a@pdborcl

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 12 15:39:12 2015

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

Last Successful login time: Mon Oct 12 2015 15:38:00 +05:30

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 opt

ions

SQL> show con_name

CON_NAME
-------------

PDBORCL

Now if we query the LSLT inside PDB and at CDB$ROOT, we see both of them have been updated.

conn /as sysdba

SQL> select last_login from dba_users where username='C##A';

LAST_LOGIN

-----------------------------------------------

12-OCT-15 03.39.17.000000000 PM +05:30

SQL> alter session set container=pdborcl;
Session altered.

SQL> select last_login from dba_users where username='C##A';

LAST_LOGIN

----------------------------------------------

12-OCT-15 03.39.17.000000000 PM +05:30

[PDBORCL routes the connection to PDBs while those to ROOTORCL points them to CDB$ROOT]

4) How to see LSLT across the containers : There is a new view CDB_USERS, which pulls user data across all the containers, including CDB$ROOT, and presents a consolidated view at CDB$ROOT [Not just DBA_USERS, there a bunch of Oracle supplied DBA_* views, which also have a corresponding CDB_* view, all aggregating data across the containers].

However, you can't query user data from other PDBs, while being connected to a PDB. You _MUST_ be connected to CDB$ROOT to be able to query data across containers. Inside a PDB, CDB_USERS view displays the same results as DBA_USERS view.

conn system/manager@pdborcl

exec dbms_lock.sleep(10);

conn system/manager@rootorcl

SQL> select last_login, decode (con_id, 1, 'CDB$ROOT', 3, 'PDBORCL') as CONTAINER_NAME from cdb_users where username = 'SYSTEM' order by 1;

LAST_LOGIN                                                         CONTAINER_NAME
----------------------------------------------- ----------------------
12-OCT-15 04.36.21.000000000 PM +05:30  PDBORCL
12-OCT-15 04.36.31.000000000 PM +05:30  CDB$ROOT

5) Read Only PDB : As explained in Part II, if the DB is open in READ ONLY mode, we don't record LSLT in in-memeory hash table V$RO_USER_ACCOUNT. However, in a multi-tenant environment, individual PDBs can be opened in READ ONLY mode, while CDB$ROOT may still be open in READ WRITE mode. In such cases, common user' logons to PDB do update LSLT at CDB$ROOT's USER$ table. However, such logons to PDB will not leave any footprint inside PDB's USER$.

connect /as sysdba
create user c##userA identified by passA container=all;
grant create session to c##userA container=all;

alter pluggable database pdborcl open read only force; <-- Open PDB in Read Only Mode

connect c##userA/passA@pdborcl
conn /as sysdba
SQL> select nvl(to_char(last_login), 'NULL') as last_login, decode (con_id, 1, 'CDB$ROOT', 3, 'PDBORCL') as CONTAINER_NAME from cdb_users where username = 'C##USERA' order by 1;

LAST_LOGIN                                                          CONTAINER_NAME

----------------------------------------------- --------------------

12-OCT-15 04.44.28.000000000 PM +05:30  CDB$ROOT

NULL                                                                         PDBORCL

6) Behavior in 12.1.0.1 : Some of the notable differences in behavior between 12.1.0.2 and 12.1.0.1 are as follows

• Common users' logon to PDB didn't cause any updates to LSLT at CDB$ROOT
• LSLT displayed on SQL*Plus invocation was the LSLT from the container, where the common user is attempting the connection against.
• Logon to a Read Only PDB would not update LSLT in ROOT's user$

Further details on Last Successful Login Time

• Part I (Introduction/Usecase in Production Environment)
• Part II (Known Limitations/Workaround)
• Part IV (Real Application Security/Direct Login Users)