{"id":992,"date":"2015-12-30T05:21:22","date_gmt":"2015-12-30T05:21:22","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=992"},"modified":"2015-12-30T05:21:22","modified_gmt":"2015-12-30T05:21:22","slug":"fgafine-grating-auditing","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=992","title":{"rendered":"FGA(Fine Grating Auditing)"},"content":{"rendered":"

SQL> conn hr\/hr<\/p>\n

SQL>@$ORACLE_HOME\/sqlplus\/demo\/demobld.sql<\/p>\n

<\/p>\n

— HR schema\uc5d0\uc11c emp,dept \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131\uc744 \ud574\uc57c\ub9cc, Auditing\uc774 \uc81c\ub300\ub85c \ub41c\ub2e4.<\/span><\/p>\n

<\/p>\n

SQL>analyze table emp compute statistics;<\/p>\n

<\/p>\n

SQL>analyze table dept compute statistics;<\/p>\n

<\/p>\n

grant select on emp to sh;<\/p>\n

grant select on dept to sh;<\/p>\n

<\/p>\n

conn system\/manager<\/p>\n

<\/p>\n

begin<\/p>\n

   dbms_fga.add_policy(<\/p>\n

         OBJECT_SCHEMA=>’HR’,<\/p>\n

         OBJECT_NAME=>’EMP’,<\/p>\n

         policy_name=>’AUD_EMP_SAL_MGR’,<\/p>\n

         AUDIT_CONDITION=>’deptno in (10,20)’,<\/p>\n

         AUDIT_COLUMN=>’sal,mgr’,<\/p>\n

         handler_schema=>’system’,<\/p>\n

         handler_module=>’INS_EMP_TRAIL’,<\/p>\n

         enable=>TRUE);<\/p>\n

   end;<\/p>\n

\/<\/p>\n

<\/p>\n

CREATE table AUD_EMP_TRAIL (<\/p>\n

  object_schema varchar2(80),<\/p>\n

  object_name varchar2(80),<\/p>\n

  policy_name varchar2(80)<\/p>\n

);<\/p>\n

<\/p>\n

CREATE PROCEDURE system.INS_EMP_TRAIL(<\/p>\n

  p_object_schema varchar2,<\/p>\n

  p_object_name varchar2,<\/p>\n

  p_policy_name varchar2)<\/p>\n

AS <\/p>\n

BEGIN<\/p>\n

   insert into system.AUD_EMP_TRAIL<\/p>\n

   values( p_object_schema,p_object_name,p_policy_name);<\/p>\n

end;<\/p>\n

\/<\/p>\n

<\/p>\n

1) sh\ub85c \uc811\uc18d<\/p>\n

   conn sh\/sh<\/p>\n

   select * from hr.emp;<\/p>\n

<\/p>\n

2) sh\ub85c \uc811\uc18d HR\uc758 EMP \ud14c\uc774\ube14\uc758 empno,ename,sal\uc744 \uc870\uac74\uc5c6\uc774 \uc870\ud68c<\/p>\n

   show user<\/p>\n

   select empno,ename,sal from hr.emp;<\/p>\n

<\/p>\n

3) system\uc73c\ub85c \uc811\uc18d<\/p>\n

   conn system\/manager<\/p>\n

   select ename from hr.emp where detpno=20;<\/p>\n

<\/p>\n

   select * from hr.emp where deptno in (10,20,30);<\/p>\n

<\/p>\n

\ub370\uc774\ud130 \ub515\uc154\ub108\ub9ac\ub97c \ud1b5\ud574  Auditing \uc815\ubcf4\uac00 \uc815\uc0c1\uc801\uc73c\ub85c \uc0dd\uc131\ub418\uc5c8\ub294 \ud655\uc778<\/p>\n

<\/p>\n

show user<\/p>\n

select to_char(timestamp,’YYYYDDHHH24MI’) as timestamp<\/p>\n

       db_user,policy_name,sql_text<\/p>\n

from dba_fga_audit_trail;<\/p>\n

<\/p>\n

select * from aud_emp_trail;<\/p>\n","protected":false},"excerpt":{"rendered":"

SQL> conn hr\/hr SQL>@$ORACLE_HOME\/sqlplus\/demo\/demobld.sql — HR schema\uc5d0\uc11c emp,dept \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131\uc744 \ud574\uc57c\ub9cc, Auditing\uc774 \uc81c\ub300\ub85c \ub41c\ub2e4. SQL>analyze table emp compute statistics; SQL>analyze table dept compute statistics; grant select on emp to sh; grant select on dept to sh; conn system\/manager begin    dbms_fga.add_policy(          OBJECT_SCHEMA=>’HR’,          OBJECT_NAME=>’EMP’,       […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[336],"tags":[530],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/992"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=992"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/992\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=992"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=992"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=992"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}