Oracle 23c New Features for DBA'sFrom 12c Onwards, DBA_users has two new columns, Last_login and Oracle_maintained
SQL> desc dba_users Name Null? Type ------------------------------- -------- ---------------------- USERNAME NOT NULL VARCHAR2(128) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(4000) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(128) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128) EXTERNAL_NAME VARCHAR2(4000) PASSWORD_VERSIONS VARCHAR2(12) EDITIONS_ENABLED VARCHAR2(1) AUTHENTICATION_TYPE VARCHAR2(8) PROXY_ONLY_CONNECT VARCHAR2(1) COMMON VARCHAR2(3) LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE ORACLE_MAINTAINED VARCHAR2(1)
About LAST_LOGIN
This is very userful when an request from user has been raised to know when was the user last logged.
IMP Note: This works only for the sessions that logged via SQLPLUS. Connect to your database as normal user and check the last_login.
BTW auditing is enabled by default.
[oracle@Geek DBA12c admin]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 19:57:16 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 and Real Application Testing options SQL> conn test/test@pdb12c Connected. SQL> col username for a15 SQL> select username,last_login from dba_users where username='TEST'; USERNAME LAST_LOGIN --------------- --------------------------------------------------------------------------- TEST 13-SEP-13 07.58.25.000000000 PM +05:30
Well, you can see the last_login details for user test. And now SQLPLUS display the last login of the user
[oracle@Geek DBA12c admin]$ sqlplus test/test@pdb12c SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 19:57:16 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 and Real Application Testing options Last Successful login time: Tue Sep 12 2013 07:58:25 +05:30 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
If you do not want this should display, you can use
[oracle@Geek DBA12c admin]$ sqlplus test/test@pdb12c -nologintime
BUT , the super privilege user last login details will not be shown,
i.e the users that are in password file, Lets check.
SQL> conn / as sysdba Connected. SQL> alter session set container=PDB12C; Session altered. SQL> grant sysdba to test; Grant succeeded. SQL> conn test/test@pdb12c as sysdba Connected. SQL> show con_name CON_NAME ------------------------------ PDB12C SQL> select username,last_login from dba_users where username='TEST'; USERNAME LAST_LOGIN --------------- --------------------------------------------------------------------------- TEST SQL>
You See! the last_login is not displayed.
About Oracle_Maintained,
This is very useful when auditing is being performed on databases and which users to exclude, since some default users can have the privileges but not others. Previous to 12c we should write custom queries and list all users exclude those are oracle default. But from 12c onwards you do not need any scripts more.
In My CDB$ROOT Database all marked as Oracle_maintained = Y USERNAME O ---------------------------------------- - SYS Y SYSTEM Y OLAPSYS Y SI_INFORMTN_SCHEMA Y AUDSYS Y DVSYS Y GSMUSER Y ORDPLUGINS Y SPATIAL_WFS_ADMIN_USR Y SPATIAL_CSW_ADMIN_USR Y XDB Y APEX_PUBLIC_USER Y SYSDG Y DIP Y OUTLN Y ANONYMOUS Y CTXSYS Y ORDDATA Y SYSBACKUP Y MDDATA Y GSMCATUSER Y GSMADMIN_INTERNAL Y LBACSYS Y XS$NULL Y SYSKM Y OJVMSYS Y APPQOSSYS Y ORACLE_OCM Y APEX_040200 Y WMSYS Y DBSNMP Y ORDSYS Y MDSYS Y DVF Y FLOWS_FILES Y
Let's check in my PDB database. As you see some users marked N (those we have created manually after the database creation)
SQL> select username,oracle_maintained from dba_users; USERNAME O ---------------------------------------- - SYS Y SYSTEM Y OLAPSYS Y SI_INFORMTN_SCHEMA Y TESTUSER N PDBADMIN N DVSYS Y AUDSYS Y GSMUSER Y ORDPLUGINS Y SPATIAL_WFS_ADMIN_USR Y SPATIAL_CSW_ADMIN_USR Y TEST N XDB Y APEX_PUBLIC_USER Y SYSDG Y DIP Y OUTLN Y ANONYMOUS Y CTXSYS Y ORDDATA Y SYSBACKUP Y MDDATA Y GSMCATUSER Y GSMADMIN_INTERNAL Y LBACSYS Y SYSKM Y XS$NULL Y OJVMSYS Y APPQOSSYS Y ORACLE_OCM Y APEX_040200 Y WMSYS Y DBSNMP Y ORDSYS Y MDSYS Y DVF Y FLOWS_FILES Y 38 rows selected. SQL> -Thanks
Geek DBA
Follow Me!!!