User login audit [message #511129] |
Thu, 09 June 2011 12:23 |
gkrishn
Messages: 506 Registered: December 2005 Location: Putty a dark screen
|
Senior Member |
|
|
Hi ,
I want to audit user connection on my reporting database, and send a report to application team on monthly basis, with a list of users who are not connected for a month and remove them.
What would be best method, i know there is LOGON trigger, or database level auditing.What would best suit my scenario?I want to read more once i find what i want. thanks
|
|
|
|
|
|
Re: User login audit [message #511136 is a reply to message #511134] |
Thu, 09 June 2011 12:55 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
gkrishn wrote on Thu, 09 June 2011 19:51Hi mike, i see that audit too many informatinos. I just need login name,logon_time,machine
When you have more for cheaper why should use something else to get less for more expensive?
Regards
Michel
[Updated on: Thu, 09 June 2011 12:56] Report message to a moderator
|
|
|
Re: User login audit [message #511137 is a reply to message #511136] |
Thu, 09 June 2011 13:02 |
gkrishn
Messages: 506 Registered: December 2005 Location: Putty a dark screen
|
Senior Member |
|
|
Swan - Yes, 3 Tier
Mike - Trying to avoid lot of any audit maintenance due to lot of writes which i dnt use.
|
|
|
|
Re: User login audit [message #511139 is a reply to message #511138] |
Thu, 09 June 2011 13:13 |
gkrishn
Messages: 506 Registered: December 2005 Location: Putty a dark screen
|
Senior Member |
|
|
Swan,not sure hw it works from appl side. but i see many 300+ RPT***USERs who connects out of which many are not being used.
|
|
|
|
Re: User login audit [message #511143 is a reply to message #511141] |
Thu, 09 June 2011 13:25 |
gkrishn
Messages: 506 Registered: December 2005 Location: Putty a dark screen
|
Senior Member |
|
|
Want to track username,LOGON time,PROGRAM,MACHINE and generate a report on monthly basis to see who are not logged in last one month time, and remove those reporting accounts.
|
|
|
|
Re: User login audit [message #511145 is a reply to message #511144] |
Thu, 09 June 2011 13:30 |
gkrishn
Messages: 506 Registered: December 2005 Location: Putty a dark screen
|
Senior Member |
|
|
I know i am free to anything on my database. But looking for a best option from others experience.I donot want to implement something and later realize there is another best alternative.
Appreciate if anyone can share what worked best for you ,in your setups.
|
|
|
|
Re: User login audit [message #511189 is a reply to message #511129] |
Fri, 10 June 2011 01:08 |
gkrishn
Messages: 506 Registered: December 2005 Location: Putty a dark screen
|
Senior Member |
|
|
Thanks Mike.
Now, this is giving me what i wanted. Any thoughts ?
SYS:DBNAME> show parameter audit
audit_file_dest /vah/oracle/admin/dbname/adump
audit_sys_operations FALSE
audit_syslog_level
audit_trail DB
and its tracked inside "dba_audit_session"
select username from dba_users
where username like '%RPT%' and username not in
(select distinct username from dba_audit_session where username like '%RPT%' and timestamp>sysdate-30);
[Updated on: Fri, 10 June 2011 01:10] Report message to a moderator
|
|
|
Re: User login audit [message #511192 is a reply to message #511189] |
Fri, 10 June 2011 01:45 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Not need of "distinct" in subquery.
You can also write it:
select username from dba_users where username like '%RPT%'
minus
select username from dba_audit_session where username like '%RPT%' and timestamp>sysdate-30
Regards
Michel
|
|
|