ADD & DROP FGA POLICY

Tablolarımızda yapılan DML işlemlerini loglayabilmek için DBMS_FGA.ADD_POLICY paketinden yararlanırız. Öncesinde audit_trail static db parametremizin ‘DB’,’EXTENDED’ olması gerekiyor. Tablo bazında fga policy lerimizi ekleyebildiğimiz gibi şema bazında da tüm tablolar için policy ekleyebiliriz ve policy leri kaldırabiliriz. İşinize yarayacak şekilde aşağıdaki procedure leri ve dinamik sorguları da paylaşıyorum. Faydası olması dileğimle.

SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile sid='*';

System altered.

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup
 ORACLE instance started.

Total System Global Area 3.4206E+10 bytes
 Fixed Size 2245512 bytes
 Variable Size 1.7314E+10 bytes
 Database Buffers 1.6710E+10 bytes
 Redo Buffers 179892224 bytes
 Database mounted.
 Database opened.
 SQL> show parameter audit_trail

NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 audit_trail string DB, EXTENDED

select * from DBA_AUDIT_POLICIES;
 SELECT 'BEGIN DBMS_FGA.ADD_POLICY(object_schema => '''
  || object_schema
  || ''', object_name =>'''
  || object_name
  || ''', policy_name =>'''
  || policy_name
  || ''', enable => TRUE, statement_types => ''INSERT, UPDATE, SELECT, DELETE'' );END;'
  FROM DBA_AUDIT_POLICIES;
 BEGIN
  DBMS_FGA.ADD_POLICY(
  object_schema => 'TEST_SCHEMA',
  object_name => 'TEST_TABLE',
  policy_name => 'FGA_POLICY01',
  enable => TRUE,
  statement_types => 'INSERT, UPDATE, SELECT, DELETE' );
  END;
 /
 SELECT 'BEGIN DBMS_FGA.drop_policy(object_schema => '''
  || object_schema
  || ''', object_name =>'''
  || object_name
  || ''', policy_name =>'''
  || policy_name
  || ''');END;'
  FROM DBA_AUDIT_POLICIES;

BEGIN
  DBMS_FGA.drop_policy(
  object_schema => 'TEST_SCHEMA',
  object_name => 'TEST_TABLE',
  policy_name => 'FGA_POLICY01');
 END;
 /

create table deneme_1 (id number);
 select * from DBA_AUDIT_POLICIES;

AUDIT SELECT,INSERT,DELETE,UPDATE ON TEST_TABLE;

INSERT INTO TEST_TABLE VALUES (10);

UPDATE TEST_TABLE SET ID=40 WHERE ID=10;

DELETE TEST_TABLE WHERE ID=20;

SELECT * FROM TEST_TABLE;

SELECT * FROM DBA_FGA_AUDIT_TRAIL WHERE DB_USER='TEST_SCHEMA';

SELECT * FROM SYS.FGA_LOG$ WHERE OBJ$SCHEMA='TEST_SCHEMA';

SELECT * FROM SYS.AUD$ WHERE OBJ$NAME = 'TEST_TABLE';

SELECT * FROM SYS.AUD$ WHERE USERID='TEST_SCHEMA';
 SELECT *
  FROM sys.aud$
  WHERE TIMESTAMP# BETWEEN TO_DATE ('21.02.2017 23:00:00',
  'dd.mm.yyyy hh24:mi:ss')
  AND TO_DATE ('21.02.2017 23:20:00',
  'dd.mm.yyyy hh24:mi:ss');
 SELECT * FROM dba_fga_audit_trail;
 CREATE OR REPLACE PROCEDURE ADD_FGA_POLICY_SCHEMA (
  S_NAME IN VARCHAR2)
 IS
  P_NAME VARCHAR2 (100);

CURSOR cur
  IS
  SELECT OWNER, TABLE_NAME
  FROM ALL_TABLES
  WHERE OWNER = S_NAME;

BEGIN
  FOR i IN cur
  LOOP
  P_NAME := i.OWNER || '_' || i.TABLE_NAME;

BEGIN
  DBMS_FGA.ADD_POLICY (
  OBJECT_SCHEMA => i.OWNER,
  OBJECT_NAME => i.TABLE_NAME,
  POLICY_NAME => P_NAME,
  AUDIT_CONDITION => NULL,
  AUDIT_COLUMN => NULL,
  STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE');
  EXCEPTION
  WHEN OTHERS
  THEN
  DBMS_OUTPUT.PUT_LINE (P_NAME||' - '||SQLERRM);
  END;
  BEGIN
  DBMS_FGA.ENABLE_POLICY (OBJECT_SCHEMA => i.OWNER,
  OBJECT_NAME => i.TABLE_NAME,
  POLICY_NAME => P_NAME,
  ENABLE => TRUE);
  EXCEPTION
  WHEN OTHERS
  THEN
  DBMS_OUTPUT.PUT_LINE (P_NAME||' - '||SQLERRM);
  END;
  END LOOP;
 END;

CREATE OR REPLACE PROCEDURE DROP_FGA_POLICY_SCHEMA (
  S_NAME IN VARCHAR2)
 IS
  P_NAME VARCHAR2 (100);

CURSOR cur
  IS
  SELECT OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME
  FROM DBA_AUDIT_POLICIES
  WHERE OBJECT_SCHEMA = S_NAME;

BEGIN
  FOR i IN cur
  LOOP
  P_NAME := i.POLICY_NAME;

BEGIN
  DBMS_FGA.drop_policy (object_schema => i.OBJECT_SCHEMA,
  object_name => i.OBJECT_NAME,
  policy_name => P_NAME);
  END;
  END LOOP;
 END;
 EXEC ADD_FGA_POLICY_SCHEMA('TEST_SCHEMA');

EXEC DROP_FGA_POLICY_SCHEMA('TEST_SCHEMA');

Audit için kullanabileceğiniz diğer sorgular;

select name,value from v$parameter
 where name like 'audit%';

select * from dba_stmt_audit_opts
 union
 select * from dba_priv_audit_opts;
 select * from dba_audit_statement;
 select *
 from dba_sys_privs
 where privilege like '%AUDIT%';

select 'audit '||name||';'
 from system_privilege_map
 where (name like 'CREATE%TABLE%'
 or name like 'CREATE%INDEX%'
 or name like 'CREATE%CLUSTER%'
 or name like 'CREATE%SEQUENCE%'
 or name like 'CREATE%PROCEDURE%'
 or name like 'CREATE%TRIGGER%'
 or name like 'CREATE%LIBRARY%')
 union
 select 'audit '||name||';'
 from system_privilege_map
 where (name like 'ALTER%TABLE%'
 or name like 'ALTER%INDEX%'
 or name like 'ALTER%CLUSTER%'
 or name like 'ALTER%SEQUENCE%'
 or name like 'ALTER%PROCEDURE%'
 or name like 'ALTER%TRIGGER%'
 or name like 'ALTER%LIBRARY%')
 union
 select 'audit '||name||';'
 from system_privilege_map
 where (name like 'DROP%TABLE%'
 or name like 'DROP%INDEX%'
 or name like 'DROP%CLUSTER%'
 or name like 'DROP%SEQUENCE%'
 or name like 'DROP%PROCEDURE%'
 or name like 'DROP%TRIGGER%'
 or name like 'DROP%LIBRARY%')
 union
 select 'audit '||name||';'
 from system_privilege_map
 where (name like 'EXECUTE%INDEX%'
 or name like 'EXECUTE%PROCEDURE%'
 or name like 'EXECUTE%LIBRARY%');
 audit select any table BY ACCESS;

select audit_option,success,failure
 from dba_stmt_audit_opts
 union
 select privilege,success,failure
 from dba_priv_audit_opts;
 select username,
  terminal,
  action_name,
  to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
  to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
  returncode
 from dba_audit_session;
 select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY')
 from dba_audit_session
 where returncode<>0
 group by username,terminal,to_char(timestamp,'DD-MON-YYYY');
 select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode
 from dba_audit_session
 group by username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode;
 select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
 from dba_audit_session
 where returncode<>0
 and not exists (select 'x'
  from dba_users
  where dba_users.username=dba_audit_session.username);

select username,
  terminal,
  action_name,
  returncode,
  to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
  to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
 from dba_audit_session
 where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') < to_date('08:00:00','HH24:MI:SS')
 or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') > to_date('19:30:00','HH24:MI:SS');
 select count(distinct(terminal)),username
 from dba_audit_session
 having count(distinct(terminal))>1
 group by username;
 select count(distinct(username)),terminal
 from dba_audit_session
 having count(distinct(username))>1
 group by terminal;