Post navigation

Do you come here often? 12.2 change in behavior for DBA_USERS.LAST_LOGIN with Proxy Authentication

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.

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.