title: Last login date for Oracle SSO users link: https://onemoretech.wordpress.com/2009/08/21/last-login-date-for-oracle-sso-users/ author: lembobro description: post_id: 267 created: 2009/08/21 13:10:48 created_gmt: 2009/08/21 13:10:48 comment_status: open post_name: last-login-date-for-oracle-sso-users status: publish post_type: post

Last login date for Oracle SSO users

No, this post won’t reveal that secret date known to all President’s since 1948 as to when the aliens will return. ORASSO.WWSSO_AUDIT_LOG_TABLE_T records every login by users authenticated through Oracle 10g Application Server (OAS) SSO. This table lives in the Infrastructure Metadata Repository database. Records go back as far as the last time it was truncated, which by default is never. The following SQL will report the last login date for each SSO each user (back to the first date recorded in the log):

[eldapo@local ~]$ sqlplus ORASSO/PAssW0rD@infradb1

* * *

SQL> select USER_NAME, MAX(LOG_DATE)

2 from ORASSO.WWSSO_AUDIT_LOG_TABLE_T
3 where ACTION = 'LOGIN'
4 group by USER_NAME;

Now obviously there’s a lot that can be done with this. My next step is going to be creating a perl script built around it that includes some nicer formatting and maybe a pull from an LDAP directory of some additional information on each user (names, e-mail address, current status, etc.). It might also be nice to actually generate some kind of report if the user’s last login falls outside the normal password expiration period. In addition, users who haven’t logged in for a very long time could be disabled as inactive. There are some useful related posts here. here and here.

Copyright 2004-2019 Phil Lembo