Home » RDBMS Server » Security » User login audit (Oracle 11g)
User login audit [message #511129] Thu, 09 June 2011 12:23 Go to next message
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 #511131 is a reply to message #511129] Thu, 09 June 2011 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AUDIT SESSION;

Regards
Michel

[Updated on: Thu, 09 June 2011 12:26]

Report message to a moderator

Re: User login audit [message #511134 is a reply to message #511129] Thu, 09 June 2011 12:51 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi mike, i see that audit too many informatinos. I just need login name,logon_time,machine
Re: User login audit [message #511135 is a reply to message #511134] Thu, 09 June 2011 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>. I just need login name,logon_time,machine

3-tier application?
EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer
Re: User login audit [message #511136 is a reply to message #511134] Thu, 09 June 2011 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gkrishn wrote on Thu, 09 June 2011 19:51
Hi 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 Go to previous messageGo to next message
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 #511138 is a reply to message #511137] Thu, 09 June 2011 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Swan - Yes, 3 Tier
then Oracle DB can not provide desired details; only application software knows about end user & originating IP#
Re: User login audit [message #511139 is a reply to message #511138] Thu, 09 June 2011 13:13 Go to previous messageGo to next message
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 #511141 is a reply to message #511139] Thu, 09 June 2011 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>i see many 300+ RPT***USERs who connects out of which many are not being used.
SO? I suspect that many or most sessions originate from Application Server systems.
What details do you desire to be recorded & save & where do those details now reside?
Re: User login audit [message #511143 is a reply to message #511141] Thu, 09 June 2011 13:25 Go to previous messageGo to next message
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 #511144 is a reply to message #511143] Thu, 09 June 2011 13:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Want to track username,LOGON time,PROGRAM,MACHINE
You are free to proceed to do so.
Re: User login audit [message #511145 is a reply to message #511144] Thu, 09 June 2011 13:30 Go to previous messageGo to next message
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 #511148 is a reply to message #511145] Thu, 09 June 2011 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best option (at database side) is AUDIT SESSION (but as Blackswan said it gives you who connect to the database n ot who connect to the application if you want to audit application connections then you have to audit from the application).
Gives you all what you mentioned with the cheapest cost.

Regards
Michel
Re: User login audit [message #511189 is a reply to message #511129] Fri, 10 June 2011 01:08 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
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
Previous Topic: Read/write access
Next Topic: Audit trail information
Goto Forum:
  


Current Time: Thu Mar 28 17:20:21 CDT 2024