SCRIPTS PLSQL/SQL DIVERSOS.

FGA_AUDIT

/*
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1011851
*/

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBHEMOCAD', 
   object_name => 'TB_HEMOTERAPICA', 
   policy_name => 'TB_HEMOTERAPICA_AUDIT' );
END;


------------- AUDITORIA NO VS01 29/01/2010  retirada em 18/10/2010---------------
begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBHEMOCAD', 
object_name => 'TB_HEMOTERAPICA', 
policy_name => 'TB_HEMOTERAPICA_AUDIT', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED,
--, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS
);
end;
---------------------------------------
--delete from sys.fga_log$ where POLICYNAME in ('DBCORP_USU_REP','DBGERAL_ENDERECOEMPRESA');
--COMMIT

truncate table sys.fga_log$;

select distinct * from dba_fga_audit_trail 
where policy_name = 'TB_HEMOTERAPICA_AUDIT';
order by policy_name;
---------------------------------------
BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBRH', 
   object_name => 'TB_SITUACAO', 
   policy_name => 'TB_SITUACAO_AUDIT' );
END;

------------- AUDITORIA NO VS06 11/11/2009 já retirada---------------
begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBRH', 
object_name => 'TB_SITUACAO', 
policy_name => 'TB_SITUACAO_AUDIT', 
audit_condition => NULL, 
audit_column => 'CO_SITUACAO', 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED
--, 
--audit_column_opts => DBMS_FGA.ALL_COLUMNS
);
end;

/*

desc sys.fga_log$

delete from sys.fga_log$ where POLICYNAME ='TB_PESSOA_AUDIT' and trunc(NTIMESTAMP#) = to_date('16-abr-2008');


desc dba_fga_audit_trail
select distinct policy_name, OBJECT_NAME, OBJECT_SCHEMA from dba_fga_audit_trail ;

select distinct TIMESTAMP, db_user, os_user, userhost, object_schema, object_name, sql_text
from dba_fga_audit_trail 
where trunc(TIMESTAMP) <= to_date('16-abr-2008') and policy_name in ('TB_PESSOA_AUDIT')
order by TIMESTAMP, db_user;

select distinct * from dba_fga_audit_trail 
where policy_name in ('TB_EMPRESA_AUDIT', 'TB_PESSOA_JURIDICA_AUDIT');
--in('IDBSVSAPREMARCA','IDBGERALMARCA','SCLASSEEMBARCACAO', 'SGERALCLASSEEMBARCACAO','SDBSISTRUBANCO', 'SDBSCINH');

desc dba_fga_audit_trail;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBCORPORATIVO', 
   object_name => 'TB_PESSOA_FISICA', 
   policy_name => 'TB_PESSOA_FISICA_AUDIT' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBCORPORATIVO', 
object_name => 'TB_PESSOA_FISICA', 
policy_name => 'TB_PESSOA_FISICA_AUDIT', 
audit_condition => NULL, 
audit_column => 'NU_CPF', 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'UPDATE', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED
--, 
--audit_column_opts => DBMS_FGA.ALL_COLUMNS
);
end;

--
BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBCORPORATIVO', 
   object_name => 'TB_PESSOA', 
   policy_name => 'TB_PESSOA_AUDIT' );
END;


begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBCORPORATIVO', 
object_name => 'TB_PESSOA', 
policy_name => 'TB_PESSOA_AUDIT', 
audit_condition => NULL,
audit_column => 'CO_BANCO,CO_AGENCIA,NU_CONTA_CORRENTE', 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'UPDATE', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED
--, 
--audit_column_opts => DBMS_FGA.ALL_COLUMNS
);
end;


--A MESMA PARA DBSAT - TB_PORTE_SAT
BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBSAT', 
   object_name => 'TB_PORTE', 
   policy_name => 'TB_PORTE_SAT' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBCORPORATIVO', 
object_name => 'TB_PORTE', 
policy_name => 'TB_PORTE_CORPORATIVO', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT, INSERT, UPDATE, DELETE', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBCORPORATIVO', 
   object_name => 'RL_APRESENTACAO_MARCA', 
   policy_name => 'DBSVSAPREMARCA' );
END;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBCORPORATIVO', 
   object_name => 'TB_UNIDADE_MEDIDA_PRODUTO', 
   policy_name => 'SUNIDPRODUTODBCORPORATIVO' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBCORPORATIVO', 
object_name => 'TB_UNIDADE_MEDIDA_PRODUTO', 
policy_name => 'SUNIDPRODUTODBCORPORATIVO', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBGERAL', 
   object_name => 'TB_GRUPO_PAIS', 
   policy_name => 'SGRUPOPAISDBGERAL' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBGERAL', 
object_name => 'TB_GRUPO_PAIS', 
policy_name => 'SGRUPOPAISDBGERAL', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBSVS', 
   object_name => 'RL_EMPRESA_SIVS', 
   policy_name => 'SRLDBSVS' );

END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBSVS', 
object_name => 'RL_EMPRESA_SIVS', 
policy_name => 'SRLDBSVS', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBSVS', 
   object_name => 'TB_PROCESSO', 
   policy_name => 'SDBSVS' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBSVS', 
object_name => 'TB_PROCESSO', 
policy_name => 'SDBSVS', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;


BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBSCINH', 
   object_name => 'TB_UNIDADE_MEDIDA', 
   policy_name => 'SDBSCINH' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBSCINH', 
object_name => 'TB_UNIDADE_MEDIDA', 
policy_name => 'SDBSCINH', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBSISTRU', 
   object_name => 'TB_BANCO', 
   policy_name => 'SDBSISTRUBANCO' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBSISTRU', 
object_name => 'TB_BANCO', 
policy_name => 'SDBSISTRUBANCO', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBSVS', 
   object_name => 'RL_APRESENTACAO_MARCA', 
   policy_name => 'idbsvsapremarca' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBSVS', 
object_name => 'RL_APRESENTACAO_MARCA', 
policy_name => 'idbsvsapremarca', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'INSERT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'DBGERAL', 
   object_name => 'TB_MARCA', 
   policy_name => 'idbgeralmarca' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'DBGERAL', 
object_name => 'TB_MARCA', 
policy_name => 'idbgeralmarca', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'INSERT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'dbcorporativo', 
   object_name => 'tb_classe_embarcacao', 
   policy_name => 'sclasseembarcacao' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'dbcorporativo', 
object_name => 'tb_classe_embarcacao', 
policy_name => 'sclasseembarcacao', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

BEGIN
sys.DBMS_FGA.DROP_POLICY(
   object_schema  => 'dbgeral', 
   object_name => 'tb_classe_embarcacao', 
   policy_name => 'sgeralclasseembarcacao' );
END;

begin
sys.DBMS_FGA.ADD_POLICY(object_schema => 'dbgeral', 
object_name => 'tb_classe_embarcacao', 
policy_name => 'sgeralclasseembarcacao', 
audit_condition => NULL, 
audit_column => NULL, 
handler_schema => NULL, 
handler_module => NULL, 
enable => TRUE, 
statement_types=> 'SELECT', 
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, 
audit_column_opts => DBMS_FGA.ALL_COLUMNS);
end;

*/