Re: Oracle Database Auditing - Some more questons

"Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0406161210.3c1be59f_at_posting.google.com...

> I have no choice but to implement Oracle database auditing. I have to
> retain
> audit data for 8 years for legal reasons. My questions are:

I have no doubt you have a legal requirement to audit and to retain those audit records. I doubt very much, however, whether the legal requirement is going to be satisfied merely by turning on Oracle's database auditing feature. I also don't see that the legal requirement demands that you retain 8 years of auditing records *within* the database. But whatever: if this is the way you feel compelled to go, then so be it.

> 1. I presume, I can alter sys.aud$ table storage parameters to hold a
> large amount of data.

Er, what makes you think a large amount of data needs storage parameters to be mucked aorund with? Move aud$ into locally managed tablespace, and have done with it.

> 2. If sys.aud$ table extends to several giga bytes. will it create
> any performance problems for database operations. As sys.aud$ table
> grows, performance of inserts in this table should not change all that
> much.

The size of aud$ is the least of your worries. Privilege auditing means performance suffers regardless.

> 3. Is it possible to keep sys.aud$ in tablespace other than system. I
> am using Oracle8i. I have read conflicting articles on keeping
> sys.aud$ table in non system tablespaces (some articles say, it is not
> Oracle supported but it can
> still be done).

Spot on. Oracle's own DBA course material recommends that you move it. But Oracle Corporation will not support you doing so. Make of that what you will!

> 4. I presume when I look in sys.aud$ using Oracle's commands, there
> are indexes on this table so I should be able to look at the
> information fast.

I wouldn't presume anything as far as the data dictionary is concerned. Use your OCP-acquired skills to test these things out. (For example, if a table is moved, its indexes are all rendered unusable. And if an index is unusable, unusual errors occur when you then try to insert into the table. So there's your test).

> 5. I know some people periodically move data from sys.aud$ table to
> some other table. My question is if I move data to some other table
> can I still use
> Oracle's audit commands/views to search into new tables or I have to
> write my
> own scripts.

You'll have to write your own scripts/views. Oracle's views expect to find data in aud$. Your data won't be there any more. Therefore Oracle's views will not return results.

> 6. I want to do some minimal security auditing for Sarbanes Oxley
> compliance,
> does any one have recommendations what "events" to audit. Right now, I
> have
> been told to audit loon/logoff, any structural changes to database,
> any password
> changes, profile changes.

Oddly enough, IIRC not one of those is captured by switching on the sort of database audting that you've been talking about so far. Which is where I came in: I don't doubt you have a legal requirement to audit. But I doubt very much that is a legal requirement to switch on AUD$ database auditing.

Regards
HJR
>
>
> Appreciate your answers,
>
> Prem
> Certified Oracle 8, 8i and 9i DBA
Received on Wed Jun 16 2004 - 16:09:44 CDT