1. Set AUDIT_TRAIL parameter value db (Default) so that audit information will written into database (SYS.AUD$).
2. Create Schema and define audit policy.
3. Create and Enable fine grained auditing.
4. Collect all SQL Statement from sys.FGA_LOG$ table.
5. Purge all Audit information.
6. Drop fine grained auditing.
7. Scripts for enable fine grained auditing for all objects of schema.
1.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL>
2.
GRANT CONNECT, RESOURCE, DBA TO AUDIT_USER IDENTIFIED BY AUDIT_USER;
AUDIT ALL BY AUDIT_USER BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY AUDIT_USER BY ACCESS;
AUDIT EXECUTE PROCEDURE BY AUDIT_USER BY ACCESS;
3.
--- Create Fine Grained Auditing
BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'AUDIT_USER',
OBJECT_NAME => 'TEST_DATA',
POLICY_NAME => 'AUDIT_USER_P',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE');
END;
--- Enable Fine Grained Auditing
BEGIN
DBMS_FGA.ENABLE_POLICY (
OBJECT_SCHEMA => 'AUDIT_USER',
OBJECT_NAME => 'TEST_DATA',
POLICY_NAME => 'AUDIT_USER_P',
ENABLE => TRUE);
END;
4.
--- Find all SQL Statement using the following Query. You can also store all SQL Statement in your own table using the Second query.
SELECT LSQLTEXT, LSQLBIND,NTIMESTAMP#
FROM FGA_LOG$;
CREATE TABLE SQL_STATEMENT
AS
SELECT LSQLTEXT, LSQLBIND,NTIMESTAMP#
FROM FGA_LOG$;
SELECT *
FROM FGA_LOG$;
5.
--- Auditing information can be purge by truncate SYS.AUD$ and sys.FGA_LOG$ table. This is manual purge. Automated purge policy can define so that Oracle will automatically purge all audit information by using scheduler.
TRUNCATE TABLE SYS.AUD$ ;
TRUNCATE TABLE sys.FGA_LOG$;
6.
BEGIN
DBMS_FGA.drop_policy(
object_schema => 'AUDIT_USER',
object_name => 'TEST_DATA',
policy_name => 'AUDIT_USER_P');
END;
7.
CREATE OR REPLACE PROCEDURE SP_AUDIT_SCHEMA_ACTIVITY (
P_IN_SCHEMA_NAME IN VARCHAR2)
IS
W_POLICY_NAME VARCHAR2 (100);
CURSOR CUR_TABLE_LIST
IS
SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = P_IN_SCHEMA_NAME;
BEGIN
FOR IDX IN CUR_TABLE_LIST
LOOP
W_POLICY_NAME := IDX.OWNER || '_' || IDX.TABLE_NAME;
IF LENGTH(W_POLICY_NAME)>30 THEN
W_POLICY_NAME:=IDX.TABLE_NAME;
END IF;
BEGIN
DBMS_FGA.ADD_POLICY (
OBJECT_SCHEMA => IDX.OWNER,
OBJECT_NAME => IDX.TABLE_NAME,
POLICY_NAME => W_POLICY_NAME,
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (W_POLICY_NAME||' >> '||SQLERRM);
END;
BEGIN
DBMS_FGA.ENABLE_POLICY (OBJECT_SCHEMA => IDX.OWNER,
OBJECT_NAME => IDX.TABLE_NAME,
POLICY_NAME => W_POLICY_NAME,
ENABLE => TRUE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (W_POLICY_NAME||' >> '||SQLERRM);
END;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE SP_DROP_AUDIT_POLICY (
P_IN_SCHEMA_NAME IN VARCHAR2)
IS
W_POLICY_NAME VARCHAR2 (100);
CURSOR CUR_POLICY_LIST
IS
SELECT OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME
FROM DBA_AUDIT_POLICIES
WHERE OBJECT_SCHEMA = P_IN_SCHEMA_NAME;
BEGIN
FOR IDX IN CUR_POLICY_LIST
LOOP
W_POLICY_NAME := IDX.POLICY_NAME;
BEGIN
DBMS_FGA.drop_policy (object_schema => IDX.OBJECT_SCHEMA,
object_name => IDX.OBJECT_NAME,
policy_name => W_POLICY_NAME);
END;
END LOOP;
END;
EXEC SP_AUDIT_SCHEMA_ACTIVITY('SCHEMA_NAME');
EXEC SP_DROP_AUDIT_POLICY('SCHEMA_NAME');
Reference
https://oracle-base.com/articles/10g/auditing-10gr2
https://oracle-base.com/articles/11g/auditing-enhancements-11gr2
2. Create Schema and define audit policy.
3. Create and Enable fine grained auditing.
4. Collect all SQL Statement from sys.FGA_LOG$ table.
5. Purge all Audit information.
6. Drop fine grained auditing.
7. Scripts for enable fine grained auditing for all objects of schema.
1.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SQL>
2.
GRANT CONNECT, RESOURCE, DBA TO AUDIT_USER IDENTIFIED BY AUDIT_USER;
AUDIT ALL BY AUDIT_USER BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY AUDIT_USER BY ACCESS;
AUDIT EXECUTE PROCEDURE BY AUDIT_USER BY ACCESS;
3.
--- Create Fine Grained Auditing
BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'AUDIT_USER',
OBJECT_NAME => 'TEST_DATA',
POLICY_NAME => 'AUDIT_USER_P',
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE');
END;
--- Enable Fine Grained Auditing
BEGIN
DBMS_FGA.ENABLE_POLICY (
OBJECT_SCHEMA => 'AUDIT_USER',
OBJECT_NAME => 'TEST_DATA',
POLICY_NAME => 'AUDIT_USER_P',
ENABLE => TRUE);
END;
4.
--- Find all SQL Statement using the following Query. You can also store all SQL Statement in your own table using the Second query.
SELECT LSQLTEXT, LSQLBIND,NTIMESTAMP#
FROM FGA_LOG$;
CREATE TABLE SQL_STATEMENT
AS
SELECT LSQLTEXT, LSQLBIND,NTIMESTAMP#
FROM FGA_LOG$;
SELECT *
FROM FGA_LOG$;
5.
--- Auditing information can be purge by truncate SYS.AUD$ and sys.FGA_LOG$ table. This is manual purge. Automated purge policy can define so that Oracle will automatically purge all audit information by using scheduler.
TRUNCATE TABLE SYS.AUD$ ;
TRUNCATE TABLE sys.FGA_LOG$;
6.
BEGIN
DBMS_FGA.drop_policy(
object_schema => 'AUDIT_USER',
object_name => 'TEST_DATA',
policy_name => 'AUDIT_USER_P');
END;
7.
CREATE OR REPLACE PROCEDURE SP_AUDIT_SCHEMA_ACTIVITY (
P_IN_SCHEMA_NAME IN VARCHAR2)
IS
W_POLICY_NAME VARCHAR2 (100);
CURSOR CUR_TABLE_LIST
IS
SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = P_IN_SCHEMA_NAME;
BEGIN
FOR IDX IN CUR_TABLE_LIST
LOOP
W_POLICY_NAME := IDX.OWNER || '_' || IDX.TABLE_NAME;
IF LENGTH(W_POLICY_NAME)>30 THEN
W_POLICY_NAME:=IDX.TABLE_NAME;
END IF;
BEGIN
DBMS_FGA.ADD_POLICY (
OBJECT_SCHEMA => IDX.OWNER,
OBJECT_NAME => IDX.TABLE_NAME,
POLICY_NAME => W_POLICY_NAME,
AUDIT_CONDITION => NULL,
AUDIT_COLUMN => NULL,
STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (W_POLICY_NAME||' >> '||SQLERRM);
END;
BEGIN
DBMS_FGA.ENABLE_POLICY (OBJECT_SCHEMA => IDX.OWNER,
OBJECT_NAME => IDX.TABLE_NAME,
POLICY_NAME => W_POLICY_NAME,
ENABLE => TRUE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (W_POLICY_NAME||' >> '||SQLERRM);
END;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE SP_DROP_AUDIT_POLICY (
P_IN_SCHEMA_NAME IN VARCHAR2)
IS
W_POLICY_NAME VARCHAR2 (100);
CURSOR CUR_POLICY_LIST
IS
SELECT OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME
FROM DBA_AUDIT_POLICIES
WHERE OBJECT_SCHEMA = P_IN_SCHEMA_NAME;
BEGIN
FOR IDX IN CUR_POLICY_LIST
LOOP
W_POLICY_NAME := IDX.POLICY_NAME;
BEGIN
DBMS_FGA.drop_policy (object_schema => IDX.OBJECT_SCHEMA,
object_name => IDX.OBJECT_NAME,
policy_name => W_POLICY_NAME);
END;
END LOOP;
END;
EXEC SP_AUDIT_SCHEMA_ACTIVITY('SCHEMA_NAME');
EXEC SP_DROP_AUDIT_POLICY('SCHEMA_NAME');
Reference
https://oracle-base.com/articles/10g/auditing-10gr2
https://oracle-base.com/articles/11g/auditing-enhancements-11gr2
