Let's automate the table trigger level data audit for Oracle.

Dear all,

Right now we are trying to do the data level audit for  Oracle, please read the PPT attached.

You all can see that for each table we need to do it one by one manually, so we would like to(perfectly):

1)	Create a store procedure to just pass in the database name.
2)&#9;Once the store procedure runs, all will create the respective script for us to create the DBAudit_<table_name> table for any tables hasn’t get the data audit setup yet.
3)&#9;Apart from the DBAudit_<table_name>, the store procedure will also generate the AFTER INSERT/UPDATE and BEFORE delete trigger script for us, let us preview and we run it ourselves.

But for this we need to:

1)&#9;Use query to find out all tables in that database, how?

  Select owner, table_name from all_tables?

  How to bypass the tables belongs to the schema SYS and SYSTEM ? how to use cursor to do it to find out all table name and save to a permanent table in oracle?

2)&#9;Use query to find out all fields on each table? I think I have to use Cursor to check the table one by one by inputting the all table from 1).

describe ADMGCUK.AFR_ADDR, for example?

Anyway to use cursor to save all field into a temp table?

3)&#9;Once 1 +2 can be done, then each time we run the store procedure, it will:
I.&#9;Scan for any new tables that DO NOT exist in the result permanent table list since last time we run the SP.
II.&#9;Scan for all fields for any new table from I.
III.&#9;Because 1) will save the result to a permanent table, we can compare the result with I. We also need to add the new table name to the permanent table for next time.
IV.&#9;Any table we found IS NOT IN the permanent table will be store in temporary table.
V.&#9;The SP will then generate the respective “create DBAudit_<source table name>…” statement as well as all INSERT/UPDATE/DELETE trigger script from any table store in the temp table and show it in the screen.
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
ad 1:
select *
  from dba_tables a
 where a.owner not in ('SYS', 'SYSTEM');

Open in new window

for temp table creation:
create table <temp_table_name> nologging as
  select *
    from dba_tables a
   where a.owner not in ('SYS', 'SYSTEM');

Open in new window

ad 2:
  select *
    from dba_tab_columns a
   where a.owner not in ('SYS', 'SYSTEM');

Open in new window

But you may want to join these 2:
  select *
    from dba_tab_columns b,
         dba_tables a
   where a.owner not in ('SYS', 'SYSTEM')
     and b.owner = a.owner
     and b.table_name = a.table_name
   order by a.table_name,

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Why not use the built-in audit facility? It's guaranteed to be more reliable and will save you a lot of unnecessary work.
marrowyungSenior Technical architecture (Data)Author Commented:
"built-in audit facility? " what you are referring to ?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

marrowyungSenior Technical architecture (Data)Author Commented:
Standard audit do not do data audit, FGA only do policy based audit (only log record if that person's salary is >USD5000), FGA still log down something hasn't been commited,

only trigger log down all update/insert/delete only when the data committed, right?
marrowyungSenior Technical architecture (Data)Author Commented:

ad1 just return all table name ?

ad 2 return this:

08:36:06 Info: You have reached your low memory threshold.  Script aborting.

how to fix it other than restart ?
marrowyungSenior Technical architecture (Data)Author Commented:
The hardest part is the field change detection, add a DDL trigger on the fire so that when source table changed on the time of deployment, the respective DBAudit_<source table> will have that field changed too?

The hardest part is what if the field has type change, but not add/drop fields.

this confuse me a lot .
slightwv (䄆 Netminder) Commented:
I suggest you contact your Oracle Account Team for this.

You can explain your exact requirements and they can work through a solution with you.

As mentioned above, Oracle Auditing and maybe another product/tool should probably be the way to go.

I can't imagine you have stricter auditing requirements than some of the Government agencies/Companies that Oracle works with.  I'm confident they have products or tools that can meet any requirement you have.

It will also likely be stronger/better than anything you could develop in-house.
I simply have to agree with slightwv and suggest you pursue the concept of using an existing, proven, audit product/tool instead of a "do it yourself" approach.

Particularly as I believe you want all of these covered: MS SQL, MySQL, Oracle and DB2.
marrowyungSenior Technical architecture (Data)Author Commented:
slightwv and PortletPaul,

Yeah, I just finsihed 1 week of Oralce training, the standared audit is rubblish, the FGA can't help on this and only trigger can do the job, this is what I am doing for MysQL and once it is done,  I will convert it to Oracle lateron.

yeseterday just done a big move to create the SP to create the table and add field automatically.

now createed a ticket on how to keep combining string, please help on that ticket.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.