We want to audit the LOGIN/LOGOFF activities on our Oracle Database 10g.
But the the AUDIT CREATE SESSION command cannot check the audit condition (filter) by APPLIACTION or PROGRAM information (in V$session for example).
In our case, we have one Oracle user (normally we called this is ONE-BIG-APPLIACTION-USER) that present for many Application Users (>= 1000 users). When we use the AUDIT CREATE SESSION to audit LOGIN activities so we got alot of audit trails of the ONE-BIG-APPLIACTION-USER that we do NOT want to audit. (This causes the performance problem).
We do not want audit the login session of ONE_BIG_APP_USER come from Web-Application, but do want to audit the login session of ONE_BIG_APP_USER if someone login directly into Oracle Database using SQL*Plus.
Is it possible to do that with the standard Oracle Audit Vault solution or could you recommend us another solution for this case ?
Audit Vault does not do auditing. Auditing is performed using the AUDIT, FGA, and other auditing facilities in your database.
Audit Vault serves as a collector and presenter of audit trails. It does not enable or disable any database auditing activities.
As we know to audit the login session, we can use Trigger & AUDIT CREATE SESSION command.
1. AUDIT CREATE SESSION by access [username]
With this way, we only audit the session by ACCESS | SESSION and USERNAME. But in my case, we would like to audit the session by APPLICATION or PROGRAM.
So this way cannot solve our problem.
With Trigger, we can write PL/SQL to filter the session by APPLICATION, PROGRAM or anythings else, and then we store these audit trails in a log table (for ex: USER_LOG). But this way do NOT support by DB Collector, because DB Collector just only extract audit trails from SYS.AUD$ and SYS.FGA$.
So instead of storing the audit trails in customized USER_LOG table, we're going insert directly these audit trails to SYS.AUD$. Is it possible ? Does DB Collector can extract the audit trails from SYS.AUD$ as normal ?
I've not test it yet, so please share your expieriences about this case.
agree with you, this is a real requirement in real system.
The solution as i described above, but not test it yet. And I would like Oracle provide the built-in solution for this.
How about your solution, bednar ?
Thanks & Regards.
If we use trigger to catched the login/off activities, and then stored it in our log table. So How the AV Collector can transfer audit trail log to AV Server automatically ?
AV Collector just transfer audit trails from SYS.AUD$ | SYS.FGA_LOG | OS files to AV Server, not from others location.
Please share your expierences on this.
My questions is again did you managed to find a solutions for your case ? We also would like to audit the application user only when he is connected from non-application-server IP address. If we turn auditing on this user in general this would cause performance issue. Thats why we seek for the same solution, how to turn on full auditing on the user if he is connected from non-app-ip and audit only this session.
Thanks in advance.
We tried to do the same and couldn't find a solution in audit vault. Be interested to know how to setup this. There is just too much noise from our app and since we trust the ip it comes from - we don't want to audit that but we need to audit if it's not from the trusted ip. we are evaluating a 3rd party tools called core audit by blue core research to do this.