In my previous post, I had briefly talked about a feature newly added in Oracle 12c. Today, I am going to elaborate more on its usage, especially the caveats/considerations, before one should start adopting its usage in production environment.
1) Administrative Users : There is no support for recording last successful logon time for administrative logons i.e, logons as SYSOPER, SYSDBA or the newly added SoD (Separation Of Duty) administrative privileges, like SYSBACKUP, SYDG, SYSKM. So if a user DBAOPER has been granted administrative privileges, say SYSOPER and tries logging into to the system "AS SYSOPER" by using Password File/Operating System based authentication, his LSLT will not be updated in USER$ and hence not visible in DBA_USERS view. So while devising rules for locking inactive users, based on LAST_LOGIN field, you must consider excluding the administrative privileged users. Also note that V$PWFILE_USERS does not have any column for LSLT unlike DBA_USERS.
2) Externally Authenticated Users : They are supported. I tried Operating System based authentication by making use of OS_AUTHENT_PREFIX parameter and SSL based authentication making use of digital certificates and in both the instances, DBA_USER had their Last Successful Login Time recorded.
3) Proxy Authentication : When a proxy user logs in (connect proxy[client]/proxyPass), the connections gets established using credentials of proxy user, but the actual login happens as client user. In other words, once the login happens, the session behaves as if, it were established using client' credentials. So the natural question is whose LSLT gets updated post successful logon ?? Is it PROXY or CLIENT or BOTH ?? Ideally, for a successful proxy logon, LSLT for both proxy and client users should be updated, because both the users account status needs to be ACTIVE in order to establish a successful proxy connection.
However, As on 12.1.0.2, this holds true only for Double Session Proxy sessions (not supported in SQL*Plus, but supported in OCI and JDBC). For Single session proxy sessions (supported in SQL*Plus, OCI and JDBC), a successful proxy logon _does not_ update LSLT for the proxy user and only CLIENT user' LSLT gets updated (I plan to cover the various types of proxy authentications some day, hopefully soon enough for my readers)
So while sifting through DBA_USERS.LAST_LOGIN, you want to do some special handling for PROXY Only users, who may only connect to the system via PROXY authentication and may not connect directly leaving behind a stale LSLT in USER$.
1) Administrative Users : There is no support for recording last successful logon time for administrative logons i.e, logons as SYSOPER, SYSDBA or the newly added SoD (Separation Of Duty) administrative privileges, like SYSBACKUP, SYDG, SYSKM. So if a user DBAOPER has been granted administrative privileges, say SYSOPER and tries logging into to the system "AS SYSOPER" by using Password File/Operating System based authentication, his LSLT will not be updated in USER$ and hence not visible in DBA_USERS view. So while devising rules for locking inactive users, based on LAST_LOGIN field, you must consider excluding the administrative privileged users. Also note that V$PWFILE_USERS does not have any column for LSLT unlike DBA_USERS.
2) Externally Authenticated Users : They are supported. I tried Operating System based authentication by making use of OS_AUTHENT_PREFIX parameter and SSL based authentication making use of digital certificates and in both the instances, DBA_USER had their Last Successful Login Time recorded.
3) Proxy Authentication : When a proxy user logs in (connect proxy[client]/proxyPass), the connections gets established using credentials of proxy user, but the actual login happens as client user. In other words, once the login happens, the session behaves as if, it were established using client' credentials. So the natural question is whose LSLT gets updated post successful logon ?? Is it PROXY or CLIENT or BOTH ?? Ideally, for a successful proxy logon, LSLT for both proxy and client users should be updated, because both the users account status needs to be ACTIVE in order to establish a successful proxy connection.
However, As on 12.1.0.2, this holds true only for Double Session Proxy sessions (not supported in SQL*Plus, but supported in OCI and JDBC). For Single session proxy sessions (supported in SQL*Plus, OCI and JDBC), a successful proxy logon _does not_ update LSLT for the proxy user and only CLIENT user' LSLT gets updated (I plan to cover the various types of proxy authentications some day, hopefully soon enough for my readers)
So while sifting through DBA_USERS.LAST_LOGIN, you want to do some special handling for PROXY Only users, who may only connect to the system via PROXY authentication and may not connect directly leaving behind a stale LSLT in USER$.
4) Read Only Database : Starting 11gR2 (11.2.0.4 patchset release to be precise), Oracle does support account lockout by honouring failed login attempts, when DB is in Read Only mode. An in-memory hash table accessible via V$RO_USER_ACCOUNT view hosts relevant information, w,r,t failed login attempts and account lock status/time. However, there is no support of recording Last Successful Login Time.
Further details on Last Successful Login Time
No comments:
Post a Comment