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.
But surely that just creates the same problem the other way around. If everyone logs in to a client account as a proxy, the client account will look dormant. This could be as big a problem, depending upon how you are using Proxy accounts. Perhaps both accounts should get touched.
Yes, I agree, my preference would be for both accounts to get updated (or an additional field).
However assuming Client Account= Personal Account (one for each real person in an organization) and Proxy Account = Application Account (may either hold Tables or Business Logic or be used for connection per best practices), then while I can see the benefit in monitoring logins of the Client/Personal Account to check for whether someone has left the company or no longer uses this database, I don’t see the benefit of monitoring logins against Proxy/Application Accounts. Just because it’s not used for connections, doesn’t mean it’s not being used. It might just be holding data or business logic, which is accessed by application connecting as a different account.
However, for sure no harm that I could see in updating last_login_time both proxy and client accounts.