Oracle Fine-Grained Auditing (Oracle FGA)

Today my first time using Oracle Fine-Grained Auditing. Why do I need to use Oracle Fine-Grained Auditing?
This is because there is a table that is accessed by many procedures, triggers and packages. So that in case of deletion, I can not track from which the “DELETE” order came from.

I’ve tried using a trigger on a table to track it down. But this action, I think is still not enough. So I tried to use Oracle FGA.

Here’s an example that I use Oracle FGA :

— truncate the audit trail log
CONN sys/password AS SYSDBA
TRUNCATE TABLE fga_log$;

— add the policy TST_AUDIT to the MIS schema all column of the TRD_STATEMENTS table
BEGIN
DBMS_FGA.add_policy (object_schema => ‘MIS’,
object_name => ‘TRD_STATEMENTS’,
policy_name => ‘TST_AUDIT’,
audit_condition => NULL,
audit_column => NULL,
statement_types => ‘DELETE’);
END;

— check the audit trail result
SELECT * FROM dba_fga_audit_trail;

Advertisements

How to exit from a procedure in Oracle

To exit from function is easy, just put “RETURN”

CREATE OR REPLACE FUNCTION MIS.FC_GET_ACCRUAL_COM(VAGR IN VARCHAR2) RETURN NUMBER IS
VNUM NUMBER;
BEGIN
SELECT AGR INTO VNUM FROM TAGR WHERE AGR = ‘AA’;

IF VNUM = 0 THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
RETURN VNUM;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/

To exit from procedure also easy, just use “RAISE”

CREATE OR REPLACE PROCEDURE TEST_EXIT
IS
HOI EXCEPTION;
BEGIN
RAISE HOI;
DBMS_OUTPUT.PUT_LINE (‘hoi’);
EXCEPTION
WHEN HOI
THEN
NULL;
END;