Fine Grained Auditing (FGA), introduced with Oracle9i, can be understood as ‘policy-based auditing’. As opposed to the standard auditing functionality, FGA lets you specify the conditions necessary for an audit record. FGA policies are programatically bound to the object (table, view) by using the ‘dbms_fga’ package.<\/p>\n
You have to have a valid set of Fine-Grained Audit policies to make it possible to specify access to the following items:<\/p>\n
Procedure<\/strong><\/p>\n begin \nCREATE OR REPLACE PACKAGE AUDIT_HANDLER CREATE OR REPLACE PACKAGE BODY AUDIT_HANDLER begin begin Source : Oracle Offical Documents<\/p>\n","protected":false},"excerpt":{"rendered":" Fine Grained Auditing (FGA), introduced with Oracle9i, can be understood as ‘policy-based auditing’. As opposed to the standard auditing functionality, FGA lets you specify the conditions necessary for an audit record. FGA policies are programatically bound to the object (table, view) by using the ‘dbms_fga’ package. You have to have a valid set of Fine-Grained…<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,12],"tags":[14,90,32],"class_list":["post-518","post","type-post","status-publish","format-standard","hentry","category-administration","category-oracle-procedure","tag-database-administration","tag-oracle","tag-oracle-administration"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-8m","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=518"}],"version-history":[{"count":10,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/518\/revisions"}],"predecessor-version":[{"id":868,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/518\/revisions\/868"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=518"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\ndbms_fga.add_policy
\n( object_schema=>’HR’
\n, object_name=>’EMPLOYEES’
\n, policy_name=>’SELECT_COUNT’
\n, audit_column => ‘FIRST_NAME’
\n, audit_condition => ”
\n, handler_schema => ‘AUDITEDUSER’
\n, handler_module => ‘AUDIT_HANDLER.HANDLE_ACCESS’
\n);
\nend;<\/p><\/blockquote>\n
\nis
\nPROCEDURE HANDLE_ACCESS
\n( object_schema VARCHAR2
\n, object_name VARCHAR2
\n, policy_name VARCHAR2
\n);
\nend;
\n\/<\/p>\n
\nis
\nPROCEDURE HANDLE_ACCESS
\n( object_schema VARCHAR2
\n, object_name VARCHAR2
\n, policy_name VARCHAR2
\n) is
\n PRAGMA AUTONOMOUS_TRANSACTION;
\nbegin
\n insert into TABLENAME
\n ( whodidit, whenwasit, sql_executed)
\n values
\n ( user, systimestamp, sys_context(‘userenv’,’current_sql’))
\n ;
\n commit;
\nend HANDLE_ACCESS;
\nend;
\n\/<\/p>\n
\n dbms_fga.add_policy
\n ( object_schema=>’SCHEMANAME’
\n , object_name=>’TABLENAME’
\n , policy_name=>’ACCESS_HANDLED’
\n , audit_column => ‘COLUMNNAME’
\n , audit_condition => ”
\n , handler_schema => ‘AUDITEDUSER’
\n , handler_module => ‘AUDIT_HANDLER.HANDLE_ACCESS’
\n );
\nend;
\n\/<\/p>\n
\n dbms_fga.drop_policy
\n ( object_schema=>’SCHEMANAME’
\n , object_name=>’TABLENAME’
\n , policy_name=>’ACCESS_HANDLED’
\n );
\nend;<\/p>\n<\/blockquote>\n