Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.