 |
 |
*********************************************************
* Description: Enable Oracle Fine-Grained Auditing [FGA]
* Date: 05:10 PM EST, 11/15/2017
*********************************************************
<1> Adding policy to enable FGA
|
|__ SQL> EXEC DBMS_FGA.ADD_POLICY(object_schema=>'CJET', object_name=>'CONTRACT_INFO',policy_name=>'audit_contract_info', statement_types=>'select, update, insert, delete');
<2> Ensure the policy created successfully:
|
|__ SQL> SELECT object_schema,object_name,policy_name,policy_column,enabled,sel,ins,upd,del FROM dba_audit_policies;
<3> Checking auding result:
|
|__ SQL> SELECT timestamp, db_user, object_schema, object_name, policy_name, statement_type, sql_bind, sql_text FROM dba_fga_audit_trail ORDER BY timestamp DESC;
TIMESTAMP DB_USER OBJECT_SCHEMA OBJECT_NAME SQL_TEXT
-------------------- -------- ------------- ------------ ---------------------------------------------------------------------------------------------------------
14-NOV-2017 20:43:25 PWDAIA IJET CONTMNT_INFO /* update com.ijet.common.domain.ContactInformation */ update CONTACT_INFO set STATUS_ID=:1,
ALERT_PIN=:2 ,PAGER=:3 , SEND_ALERTS_TO_PAGER=:4 , DAY_PHONE=:5 , SEND_ALERTS_TO_DAY_PHONE=:6 ,
EMAIL1=:7,ALERT_EMAIL_FORMAT1=:8 , SEND_ALERTS_TO_EMAIL1=:9 , EMAIL2=:10 , ALERT_EMAIL_FORMAT2=:11,
SEND_ALERTS_TO_EMAIL2=:12 , EMAIL3=:13 , ALERT_EMAIL_FORMAT3=:14 , SEND_ALERTS_TO_EMAIL3=:15 ,
EMERGENCY_CONTACT=:16 , EMERGENCY_EMAIL=:17 , EMERGENCY_PHONE=:18 , EVENING_PHONE=:19 ,
SEND_ALERTS_TO_EVENING_PHONE=:20 , FAX=:21 , SEND_ALERTS_TO_FAX=:22 , STREET=:23 , CITY=:24 ,
COUNTRY=:25 , STATE=:26 , ZIPCODE=:27 where CONTACT_INFO_ID=:28
#1(1):1 #2(0): #3(0): #4(1):0 #5(0): #6(1):0 #7(25):GEET.PIION2@BEODAY.COM #8(1):1 #9(1):1 #10(0):
#11(1):1 #12(1):0 #13(0): #14(1):1 #15(1):0 #16(10):METTE FLOR #17(0): #18(13):1-165186434418
#19(0): #20(1):0 #21(0): #22(1):0 #23(0): #24(0): #25(0): #26(0): #27(0): #28(9):635841023
14-NOV-2017 20:43:24 CDEAIA IJET CONTMNT_INFO /* insert com.ijet.common.domain.ContactInformation */ insert into COEEACT_INFO (STATUS_ID, ALERT_PIN,
PAGER, SEND_ALERTS_TO_PAGER, DAY_PHONE, SEND_ALERTS_TO_DAY_PHONE, EMAIL1, ALERT_EMAIL_FORMAT1,
SEND_ALERTS_TO_EMAIL1, EMAIL2, ALERT_EMAIL_FORMAT2, SEND_ALERTS_TO_EMAIL2, EMAIL3, ALERT_EMAIL_FORMAT3,
SEND_ALERTS_TO_EMAIL3, EMERGENCY_CONTACT, EMERGENCY_EMAIL, EMERGENCY_PHONE, EVENING_PHONE,
SEND_ALERTS_TO_EVENING_PHONE,FAX, SEND_ALERTS_TO_FAX, STREET, CITY, COUNTRY, STATE, ZIPCODE,
COEEACT_INFO_ID) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 ,
:16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 )
#1(1):1 #2(0): #3(13):+1-4102185306 #4(1):0 #5(0): #6(1):0 #7(20):PAHFA.HEI@LOUHND.COM #8(1):1 #9(1):1
#10(0): #11(1):1 #12(1):0 #13(0): #14(1):1 #15(1):0 #16(0): #17(0): #18(15):+1-841-974-7050 #19(0):
#20(1):0 #21(0): #22(1):0 #23(0): #24(0): #25(0): #26(0): #27(0): #28(9):838318413
<4> Drop the policy to stop FGA auditing:
|
|__ SQL> EXEC DBMS_FGA.DROP_POLICY(object_schema=>'CJET', object_name=>'CONTRACT_INFO', policy_name=>'audit_contract_info');
<5> Clean up auditing data:
|
|__ SQL> SET serveroutput ON SIZE 999999
BEGIN
IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD)
THEN
DBMS_output.put_line('True');
ELSE
DBMS_output.put_line('False');
END IF;
END;
/
FALSE
<6> Reference:
|
|__ http://blog.yannickjaquier.com/oracle/fine-grained-auditing-fga-hands-on.html
|
|__ https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_fga.htm#CDEIECAG
|
|__ https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3115.htm#REFRN23075
Your Comments