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.
Who is Participating?
Alexander Eßer [Alex140181]Connect With a Mentor 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

Why not use the built-in audit facility? It's guaranteed to be more reliable and will save you a lot of unnecessary work.
marrowyungAuthor Commented:
"built-in audit facility? " what you are referring to ?
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

marrowyungAuthor 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?
marrowyungAuthor 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 ?
marrowyungAuthor 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)Connect With a Mentor 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.
PortletPaulConnect With a Mentor Commented:
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.
marrowyungAuthor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.