*********************************************************
* 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