First, create the owner of the audit tables (in our scenario, will be "john_audit" and the audited schema will be "john"). Be aware that the audit user should mandatory name like audited name + "_audit".
CREATE OR REPLACE PROCEDURE create_audit_tables (table_owner VARCHAR2)
IS
CURSOR c_tables (
table_owner VARCHAR2)
IS
SELECT ot.owner AS owner, ot.table_name AS table_name
FROM all_tables ot
WHERE lower( ot.owner) = lower(table_owner)
;
aud_user varchar2(100);
v_sql VARCHAR2 (8000);
v_count1 number;
v_count2 number;
v_count NUMBER := 0;
v_aud VARCHAR2 (30);
n_count number;
BEGIN
aud_user:=table_owner||'_AUDIT';
select count(*) into v_count1 from dba_tables where lower(owner)=lower(table_owner);
select count(*) into v_count2 from dba_tables where lower(owner)=lower(aud_user);
v_count:=v_count1-v_count2;
if v_count>0 then
FOR r_table IN c_tables (table_owner)
LOOP
BEGIN
v_aud := r_table.table_name;
select count(*) into n_count from dba_tables where lower(table_name)=lower(v_aud) and lower(owner)=lower(aud_user);
if n_count=0 then
v_sql :=
'create table '||aud_user
|| '.'
|| v_aud
|| ' as select * from '
|| r_table.owner
|| '.'
|| r_table.table_name
|| ' where 0 = 1';
DBMS_OUTPUT.put_line ('Info: ' || v_sql);
EXECUTE IMMEDIATE v_sql;
v_sql :=
'alter table '||aud_user||'.'
|| v_aud
|| ' add ( AUDIT_ACTION char(1), AUDIT_BY varchar2(50), AUDIT_AT TIMESTAMP)';
EXECUTE IMMEDIATE v_sql;
end if;
-- v_count := c_tables%ROWCOUNT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Failed to create table '
|| v_aud
|| ' due to '
|| SQLERRM);
END;
END LOOP;
end if;
IF v_count = 0
THEN
DBMS_OUTPUT.put_line ('No audit tables created');
ELSE
DBMS_OUTPUT.put_line (v_count || ' audit tables created.');
END IF;
END;
/
Run the procedure (will create audit tables in audit schema only for the tables created after the last run):
begin
create_audit_tables('john'); --it's not caps sensitive
end;
/
Now, a function which will gatter the columns together:
create or replace FUNCTION get_columns_for_table (
table_owner VARCHAR2,
t_name VARCHAR2,
prefix VARCHAR2
) RETURN CLOB
IS
v_text CLOB;
BEGIN
FOR getrec IN (SELECT column_name
FROM all_tab_columns
WHERE table_name = t_name
AND owner = table_owner
AND data_type<>'BLOB')
LOOP
v_text := v_text
|| ','
|| prefix
|| getrec.column_name
|| CHR (10)
|| ' ';
END LOOP;
RETURN ltrim(v_text,',');
END;
And a function that will compare the old and new columns values, in order to find if an update applied.
create or replace function get_column_comparison (
table_owner VARCHAR2,
t_name VARCHAR2
) RETURN CLOB
IS
v_text CLOB;
BEGIN
FOR getrec IN (SELECT column_name
FROM all_tab_columns
WHERE table_name = t_name
AND owner = table_owner
AND data_type<>'BLOB')
LOOP
v_text := v_text
|| ' or( (:old.'
|| getrec.column_name
|| ' <> :new.'
|| getrec.column_name
|| ') or (:old.'
|| getrec.column_name
|| ' IS NULL and :new.'
|| getrec.column_name
|| ' IS NOT NULL) or (:old.'
|| getrec.column_name
|| ' IS NOT NULL and :new.'
|| getrec.column_name
|| ' IS NULL))'
|| CHR (10)
|| ' ';
END LOOP;
v_text := LTRIM (v_text, ' or');
RETURN v_text;
END;
And now the procedure that will produce the triggers:
CREATE OR REPLACE PROCEDURE create_audit_triggers (table_owner VARCHAR2)
IS
CURSOR c_tab_inc (
table_owner VARCHAR2)
IS
SELECT ot.owner AS owner, ot.table_name AS table_name
FROM all_tables ot
WHERE lower (ot.owner) =lower ( table_owner)
;
aud_user varchar2 (100);
v_query VARCHAR2 (32767);
v_count NUMBER := 0;
BEGIN
aud_user:=table_owner||'_AUDIT';
-- the triggers should have privileges in the audit schema
FOR c IN (SELECT owner, table_name FROM all_tables WHERE lower(owner)=lower(aud_user))
LOOP
EXECUTE IMMEDIATE 'grant select,insert,update on '||c.owner||'.'||c.table_name||' to '||table_owner;
END LOOP;
FOR r_tab_inc IN c_tab_inc (table_owner)
LOOP
BEGIN
v_query :=
'CREATE OR REPLACE TRIGGER '
||table_owner
|| '.A'
|| substr(r_tab_inc.table_name,1,29)
|| ' AFTER INSERT OR UPDATE OR DELETE ON '
|| r_tab_inc.owner
|| '.'
|| r_tab_inc.table_name
|| ' FOR EACH ROW'
|| CHR (10)
|| 'DECLARE '
|| CHR (10)
|| ' v_user varchar2(30):=null;'
|| CHR (10)
|| ' v_action varchar2(15);'
|| CHR (10)
|| 'BEGIN'
|| CHR (10)
|| ' SELECT SYS_CONTEXT (''USERENV'', ''session_user'') session_user'
|| CHR (10)
|| ' INTO v_user'
|| CHR (10)
|| ' FROM DUAL;'
|| CHR (10)
|| ' if inserting then '
|| CHR (10)
|| ' v_action:=''INSERT'';'
|| CHR (10)
|| ' insert into '
|| aud_user
|| '.'
|| r_tab_inc.table_name
|| '('
|| get_columns_for_table (r_tab_inc.owner,
r_tab_inc.table_name,
NULL)
|| ' ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
|| CHR (10)
|| ' values ('
|| get_columns_for_table (r_tab_inc.owner,
r_tab_inc.table_name,
':new.')
|| ' ,''I'',v_user,SYSDATE);'
|| CHR (10)
|| ' elsif updating then '
|| CHR (10)
|| ' v_action:=''UPDATE'';'
|| CHR (10)
|| ' if '
|| get_column_comparison (r_tab_inc.owner, r_tab_inc.table_name)
|| ' then '
|| CHR (10)
|| ' insert into '
||aud_user
||'.'
|| r_tab_inc.table_name
|| '('
|| get_columns_for_table (r_tab_inc.owner,
r_tab_inc.table_name,
NULL)
|| ' ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
|| CHR (10)
|| ' values ('
|| get_columns_for_table (r_tab_inc.owner,
r_tab_inc.table_name,
':new.')
|| ' ,''U'',v_user,SYSDATE);'
|| CHR (10)
|| ' end if;'
|| ' elsif deleting then'
|| CHR (10)
|| ' v_action:=''DELETING'';'
|| CHR (10)
|| ' insert into '
||aud_user
||'.'
|| r_tab_inc.table_name
|| '('
|| get_columns_for_table (r_tab_inc.owner,
r_tab_inc.table_name,
NULL)
|| ' ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
|| CHR (10)
|| ' values ('
|| get_columns_for_table (r_tab_inc.owner,
r_tab_inc.table_name,
':old.')
|| ' ,''D'',v_user,SYSDATE);'
|| CHR (10)
|| ' end if;'
|| CHR (10)
|| 'END;';
DBMS_OUTPUT.put_line (
'CREATE TRIGGER '
|| REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_'));
EXECUTE IMMEDIATE v_query;
DBMS_OUTPUT.put_line (
'Audit trigger '
|| REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_')
|| ' created.');
v_count := c_tab_inc%ROWCOUNT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Failed to create audit trigger for '
|| r_tab_inc.owner
|| '.'
|| r_tab_inc.table_name
|| ' due to '
|| SQLERRM);
END;
END LOOP;
IF v_count = 0
THEN
DBMS_OUTPUT.put_line ('No audit triggers created');
END IF;
END;
/
Notice the sequence which grant the audited user to modify any table from the audit schema, created before with the create_audit_tables procedure.
Now, all you have to do is to run the procedure and you will create as many triggers as tables they are. The triggers will be either created or replaced, so the procedure will run for every single table in the schema, no mother if it was created after the last run of the
create_audit_tables procedure or not.
begin
CREATE_AUDIT_TRIGGERS('john');
end;
/
Niciun comentariu:
Trimiteți un comentariu