The behavior of the LAST_LOGIN field on DBA_USERS has changed with respect to proxy authentication (for the better I think).
Proxy authentication is an feature of the Oracle Database that effective allows you to be connected as one user (the client user to use Oracle’s terminology), but using the credentials of another user (the proxy user). This is useful in combination with using personal accounts (one for every user) as the proxy users, using application accounts as the client users, avoiding the need for users to share application account passwords.
The test-case below demonstrates that when using proxy authentication in 12.1, the last login for the client user (only) is updated.
[oracle@lnx-ora121 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 17 05:03:55 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. 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> create user proxy_user identified by proxy_user; User created. SQL> create user client_user identified by client_user; User created. SQL> grant create session to proxy_user; Grant succeeded. SQL> grant create session to client_user; Grant succeeded. SQL> alter user client_user grant connect through proxy_user; User altered. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@lnx-ora121 ~]$ sqlplus proxy_user[client_user]/proxy_user SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 17 05:05:13 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. 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> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@lnx-ora121 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 17 05:05:45 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. 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> select last_login from dba_users where username = 'PROXY_USER'; LAST_LOGIN --------------------------------------------------------------------------- SQL> select last_login from dba_users where username = 'CLIENT_USER'; LAST_LOGIN --------------------------------------------------------------------------- 17-SEP-18 05.05.33.000000000 AM +00:00 SQL>
Contrast this behavior with that of the same test running on 12.2 and you can see that now it is the proxy user that has their last login time updated.
[oracle@lnx-ora122 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 17 05:04:22 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> create user proxy_user identified by proxy_user; User created. SQL> create user client_user identified by client_user; User created. SQL> grant create session to proxy_user; Grant succeeded. SQL> grant create session to client_user; Grant succeeded. SQL> alter user client_user grant connect through proxy_user; User altered. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@lnx-ora122 ~]$ sqlplus proxy_user[client_user]/proxy_user SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 17 05:06:25 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@lnx-ora122 ~]$ sqlplus proxy_user[client_user]/proxy_user SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 17 05:07:18 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. ^CERROR: ORA-28547: connection to server failed, probable Oracle Net admin error [oracle@lnx-ora122 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 17 05:07:42 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select last_login from dba_users where username = 'PROXY_USER'; LAST_LOGIN --------------------------------------------------------------------------- 17-SEP-18 05.07.05.000000000 AM +00:00 SQL> select last_login from dba_users where username = 'CLIENT_USER'; LAST_LOGIN --------------------------------------------------------------------------- SQL
Previously if an account was only being used as a proxy user, there was no way of knowing it was actually being used (without implementing a login trigger and storing the login time in a separate table). With this change we can know for such a user account if and when it is being used.
