Thanks for the question, Paul.
Asked: November 12, 2017 - 3:33 pm UTC
Last updated: November 13, 2017 - 1:03 am UTC
Version: 11.2.0.4
Viewed 10K+ times! This question is
You Asked
We are running versions 11.2.0.4 12.1 12.2. I am looking for a generic solution to capture last login date.
I think the best solution would be an initial load and then maintain the information via an after logon trigger, which will contain a merge statement that does an update or insert.
Create table last_login
(Username varchar2(30),
Os_username varchar2(30),
Last_logon_date date,
Source_ind varchar2(1),
CONSTRAINT "LAST_LOGON_PK" PRIMARY KEY ("USERNAME"))
want to join
dba_users with dba_audit_trail, using the date of the most recent audited logon (if available) to populate LAST_LOGON_DATE. If no audit record existed for a given user, I want to populate LAST_LOGON_DATE with sysdate. SOURCE_IND is an 'A' (Audit) if the last logon was from the audit trail on the initial load, a 'D' (Default) if from sysdate on the initial load, and when updated by the trigger, SOURCE_IND is set to 'T' (Trigger).
Does this sound like a good approach. Can this be done in Sam or do i need to use plsql.
and Connor said...
I'm a bit confused by
If no audit record existed for a given user,
If you have the ability to create login triggers on the database, I'm assuming you'll also have the ability to run "AUDIT CONNECT", so I'd be inclined to restrict your logic to only 2 elements:
1) Am I on 12c or above, then use DBA_USERS.LAST_LOGIN
2) If not, get the data from DBA_AUDIT_TRAIL, and not have any login triggers
What comes after that depends on requirements. If it is just for security audit, then you're done.
If you want to *display* this to users via application etc, then you might need an extension to avoid regularly scanning AUD$ (the table that underpins DBA_AUDIT_TRAIL). But only then would I start thinking about additional logic (eg login triggers)
Is this answer out of date? If it is, please let us know via a Comment