Oracle Audit

[SPN]

We are running our applications using custom built menu system. There is only one oracle user and the menu access is contolled thro' application menu roles. This kind of set up is posing some problem when it comes to auditing. When we enable oracle audit, it will show only one user for all the database activities. My question:

With the esisting set up, Is there any way we can circumvent the auditing issues.

or

Should I have to create individual Oracle users..In that case I have to create around 250 users.

Thanks,

SPN

[julian]

You are hitting a problen similar to one of mine :-)

Depending on what you want to audit, you could use triggers if you want some detailed log history about what these 250 users have deleted, updated, etc. If you content just basic information, normal auditting could suffice.

[SPN]

If you enable normal auditing , you will see only one user(oracle) in the audit table irrespective of the users. As far as the oracle is concerned only one user is transacting with Oracle database. Could you please let me know how you have implemented this set up.

Regards,

SPN

[SPN]

Could some one offer some guidlines on my topic ?

Regards,

SPN

[jmodic]

I think the easiest way for you would be to create separate oracle acount for each of your users (if this is possible at all in your situation) and take the advantages of the standard auditing.

If this option is not a viable one, and you are forced to have only one schema for all of your application users, then you have a couple of other options:

Option 1)
Take a look at your AUD$ table data collected. There are two columns - USERHOST and TERMINAL - that might be sufficient in some situations to identified which OS user has performed that particular action. However I doubt you'll find this as a sufficient solution.

Option2)
For the following option the prerequisit is that all of your application users connect to your network under distinct accounts - it is of no use if they all log on with the same OS account as welll. You can easily verify this if you query V$SESSION and check column OSUSER to see if there are distinct OS usernames in there.

The sollution itself requires little hacking on database, so you must be aware that the following is *totaly unsuported* by Oracle and that in case of troubles Oracle Supprt might (and probably will) refuse to help you as a consequence. Anyway, it is a good excersise to try this on your test (aka junk) database. The sollution involves putting INSERT ROW TRIGGER on AUD$. But since you can't put any trigger on object owned by SYS, you must first move AUD$ out of the SYS schema. The procedure is as follows:

a) rename SYS.AUD$ to something else, like SYS.AUD$OLD
b) create the same table AUD$ in some other schema, like SYSTEM; dont forget to create the index on it too
c) create synonym SYS.AUD$ to point to SYSTEM.AUD$

Now your auditing would be on again, but instead into table SYS.AUD$ the records will be inserted into table SYSTEM.AUD$. So now you have all set to create a BEFORE INSERT FOR EACH ROW trigger on SYSTEM.AUD$. The idea is to replace column USERID with the value of V$SESSION.OSUSER for that particular session. To avoid searching for that value in V$SESSION with every insert into AUD$ it would be vise to also create an AFTER LOGON trigger that would save the value of V$SESSION.OSUSER into some package variable, so that you can reference that variable into your trigger, like:

CREATE OR REPLACE TRIGGER aud_bir_trigger
BEFORE INSERT ON system.aud$ FOR EACH ROW
BEGIN
:NEW.userid := my_package.v_osuser;
END;

So now instead of having the same Oracle's username in all of your AUD records you'll have distinct OS username for each of your users.

But once more: This is totaly unuported, you might be risking your support contract by doing it! However it should work, I did something similar in the (far) past myself.

Option 3)
You still have an option to create triggers on all your tables that should be audited and create your own audit trial. However you would still need to rely on OS usernames to distinguish your application users. Again you would probably need to create AFTER LOGON trigger to capture V$SESSION.OSUSER once per session into a package variable and then use it in your audit triggers.