Last logon details of a user

[meeradevi]

Is it possible to determine the last login date and time by a user into an oracle instance.

I do not need any suggestion for future use but want to determine when a list of my existing users had last logged on into my oracle instance.

Urgent !

 

[SantaMufasa]

Meeradevi,

Since you are posting in the "Oracle 5,6, & 7" forum, then you may be out of luck. But if your version of Oracle is at least Oracle 8i, then, yes, you can audit logins by user and last-login time. I do that auditing on every instance we have. And it is extremely useful...it helps us determine which users we can drop and whick users to keep.

Let us know to what version of Oracle your need applies.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[meeradevi]

I am using Oracle Enterprise Edition 8.0.6.2.0

 

[SantaMufasa]

I believe that 8i (vers. 8.1.x), at minimum, is necessary to audit logins.

(BTW, is there a good reason that you are using a version from ten years ago? Doesn't your company want to use all the cool features in Oracle 9i and 10g that they've paid for (if they are current on their maintenance)?)

Let me know if you want to persue the login-audit solution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[meeradevi]

Mufasa...Good question...I am supporting a sunsetting application (ends mid 2007)and the company does not want to invest further...

Let me know if there is one in Oracle 8

Thanks for your help anyway !

 

[SantaMufasa]

Does that mean that the company does not have a maintenance agreement with Oracle? If that is the case, then I can understand their decision.

If you are running 8.0.6, then having Oracle audit logins will take so much "thinking outside the box", that I'm not willing to do that much thinking "for free" right now.

Sorry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[flstffatboy]

Meeradevi,

We have a 7.3.4 Oracle database which we set audit_trail = DB in the parameter file then issued the audit session command. I use the sys.dba_audit_session view which I use to check last successful and unsuccessful login attempts through a script.

I can give you more details if needed.

FLSTFFATBOY

 

[SantaMufasa]

Flstf,

Regardless of what Meeradevi wants to do, I'd be pleased for you to share code of your method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[flstffatboy]

Mufasa,

Not a problem. Basically this was to satisfy an audit requirement to track unsuccessful database login attempts for SARBOX at my company who is still running (against my wishes) Oracle 7.

set audit_trail = DB
The audit session command was issued

Below is the script I use to capture the data. I had trouble finding all the possible return code values from the dba_audit_session view but after scouring enough documentation on Metalink I was able to find some.

select upper(username) username,
upper(os_username) os_username,
terminal,
decode(min(returncode), 0, to_char(max(timestamp), 'MM/DD/RRRR HH24:MI:SS'), null) Success,
decode(returncode, 1017, to_char(timestamp, 'MM/DD/RRRR HH24:MI:SS'), null) Unsuccess,
to_char(sysdate, 'DD-MON-RRRR HH24:MI:SS') rpt_date,
to_char(sysdate, 'DD-MON-RRRR') to_date,
to_char(sysdate - 7, 'DD-MON-RRRR') from_date
from sys.dba_audit_session
group by
upper(username),
upper(os_username),
terminal,
decode(returncode, 1017, to_char(timestamp, 'MM/DD/RRRR HH24:MI:SS'), null)
order by upper(username), upper(os_username), terminal, Success
/


FLSTF

 

[flstffatboy]

Mufasa,

Cataudit.sql was also run against the database.

FLSTF