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;
Orhan Eripek's Oracle Blog 