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;