Tip
2010.04.09 10:43

10G FGA AUDIT

조회 수 23587 추천 수 0 댓글 1

PURPOSE
~~~~~~~

This bulletin explains the enhancements on Fine Grained Auditing in 10g version.

 
SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~

For DBAs and Application Administrators using FGA to audit data manipulation.

What is New ?
~~~~~~~~~~~
 
9i  FGA provides support for SELECT statements only.
10g FGA extends in the following ways:

    --> Support for DML statements :
        A. INSERT
        B. UPDATE
        C. DELETE
       (see MERGE behavior in D: it is not considered as a single DML statement )

    --> Support for more than one relevant column in an FGA policy :

        if any one of the audit columns is present in the DML statement, it is
        audited by default. 
 
        An option is provided to audit based on whether ANY or ALL of the
        relevant columns are used in the statement:
       
           audit_column_opts => DBMS_FGA.ALL_COLUMNS / DBMS_FGA.ANY_COLUMNS
 
    --> Audit trail writes the SQL text and SQL bind information to LOBs.

        You have the possibility to perform FGA without this LOB information
        overhead if this represents a huge performance impact :

        2 ways to populate or not populate the SQLBIND and SQLTEXT CLOB columns in:

        --> both SYS.AUD$ and SYS.FGA_LOG$ tables , set the AUDIT_TRAIL to 
            DB_EXTENDED value :
            see  Note 249438.1 10G  New Value DB_EXTENDED for the AUDIT_TRAIL init.ora Parameter

        --> the SYS.FGA_LOG$ table, use the AUDIT_TRAIL parameter in
            DBMS_FGA.ADD_POLICY procedure and set it to DBMS_FGA.DB_EXTENDED
            value.

Below the new parameters of the 10g DBMS_FGA.ADD_POLICY procedure :

      dbms_fga.add_policy ( object_schema => 'PIET',  object_name => 'EMP',
                            policy_name => 'MYPOLICY1', audit_condition => NULL,
                            audit_column => 'SALARY,COMMISSION_PCT',
                            audit_column_opts => DBMS_FGA.ALL_COLUMNS,
                            audit_trail => DBMS_FGA.DB_EXTENDED,
                            statement_types => 'INSERT, UPDATE');


                         -----------------------------
                         A. Behavior of INSERT and FGA
                         -----------------------------

     SQL> connect piet/piet
     Connected.

     SQL> CREATE TABLE EMP (
          EMPNO         NUMBER(4) NOT NULL,
          ENAME         VARCHAR2(10),
          JOB           VARCHAR2(9),
          MGR           NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
          HIREDATE      DATE,
          SAL           NUMBER(7,2),
          COMM          NUMBER(7,2),
          DEPTNO        NUMBER(2) NOT NULL,
          CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

     Table created.

     SQL> INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NU;
     1 row created.
     ...

     SQL> grant all on emp to miller;
     Grant succeeded.

          conn system/manager  
 
     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
             object_schema   => 'PIET', -
             object_name     => 'EMP', -
             policy_name     => 'mypolicy1', -
             audit_condition => 'sal < 1000', -
             audit_column    => 'comm', -
             enable          => TRUE,  -
             statement_types => 'INSERT');

     PL/SQL procedure successfully completed.

     SQL> select * from DBA_AUDIT_POLICY_COLUMNS ;

     OBJECT_SCHEMA                  OBJECT_NAME                                     
     ------------------------------ ------------------------------                  
     POLICY_NAME                    POLICY_COLUMN                                   
     ------------------------------ ------------------------------                  
     PIET                           EMP                                             
     MYPOLICY1                      COMM            

     SQL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,
                 POLICY_COLUMN, ENABLED, SEL, INS, UPD, DEL
          from DBA_AUDIT_POLICIES ;

     OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT POLICY_COLUMN ENA SEL INS UPD DEL
     ------------- ----------- ----------- ----------- ------------- --- --- --- --- ---                                                                   
     PIET          EMP         MYPOLICY1   sal < 1000  COMM          YES NO  YES NO  NO

     SQL> conn miller/miller
     Connected.

---------------------------------
1.1 sal < 1000 ==> INSERT audited
---------------------------------

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
          VALUES(1000, 'SAM', 800, 15, 10);
     1 row created.

-------------------------------------
1.2 sal = 1000 ==> INSERT not audited
-------------------------------------

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
          VALUES(3000, 'TOM', 20000, 1000, 20);
     1 row created.

-----------------------------------------------------------
1.3 Audit column comm is not present --> INSERT not audited
-----------------------------------------------------------

     SQL> INSERT INTO PIET.EMP (EMPNO, ENAME, SAL,DEPTNO)
          VALUES (1111, 'RAMA', 98,30);
     1 row created.

     SQL> commit;
     Commit complete.

          conn system/manager

     SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,
                 POLICY_NAME,SQL_TEXT
          from dba_fga_audit_trail ;


     DB_USER  SCHEMA   OBJECT POLICY_NAME
     -------- -------- ------ ------------------------------
     SQL_TEXT
     -------------------------------------------------------
     MILLER   PIET     EMP    MYPOLICY1
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
     VALUES(1000, 'SAM', 800, 15, 10)

------------------------------------------------------------------------
1.4 The Audit Condition can contain functions: SYSDATE, UID, USER, ROUND
------------------------------------------------------------------------

Example with ROUND function:
         execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');


     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
             object_schema   => 'PIET', -
             object_name     => 'EMP', -
             policy_name     => 'mypolicy1', -
             audit_condition => 'round(sal, -2) >= 3000 ', -
             audit_column    => 'comm', -
             enable          => TRUE,  -
             statement_types => 'INSERT');

     PL/SQL procedure successfully completed.

     SQL> connect miller/miller
     Connected.

     --------------------------------------------------------------------
     1.4.1 ROUND(2979, -2) = 3000 ==> INSERT audited
           ROUND(2949, -2) < 3000 ==> INSERT not audited
     --------------------------------------------------------------------

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
          VALUES(5000, 'RUDY', 2979, 15, 10);
     1 row created.

     SQL> commit;
     Commit complete.

          conn system/manager

     SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,
                 POLICY_NAME,SQL_TEXT
          from dba_fga_audit_trail ;


     DB_USER  SCHEMA   OBJECT POLICY_NAME
     -------- -------- ------ ------------------------------
     SQL_TEXT
     -------------------------------------------------------
     MILLER   PIET     EMP    MYPOLICY1
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
     VALUES(1000, 'SAM', 800, 15, 10)

     MILLER   PIET     EMP    MYPOLICY1
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
     VALUES(5000, 'RUDY', 2979, 15, 10)


Example with USER function:

          execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');


     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
             object_schema   => 'PIET', -
             object_name     => 'EMP', -
             policy_name     => 'mypolicy1', -
             audit_condition => 'ename=USER ', -
             audit_column    => 'comm', -
             enable          => TRUE,  -
             statement_types => 'INSERT');

     PL/SQL procedure successfully completed.

     SQL> connect miller/miller
     Connected.

     -----------------------------------------------------------------------
     1.4.2 ename = 'MILLER' ==> INSERT not audited since connected as PIET
     -----------------------------------------------------------------------
     SQL> conn piet/piet
     Connected.
     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
          VALUES(89, 'MILLER', 800, 1000, 10);

     1 row created.

     SQL> commit;
     Commit complete.

     ---------------------------------------------------------------------
     1.4.3 ename = 'MILLER' ==> INSERT audited since connected as MILLER
     ---------------------------------------------------------------------

     SQL> conn miller/miller
     Connected.
     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
          VALUES(69, 'MILLER', 700, 700, 10);

     1 row created.

     SQL> commit;
     Commit complete.


          con system/manager

     SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,
                 POLICY_NAME,SQL_TEXT
          from dba_fga_audit_trail ;


     DB_USER  SCHEMA   OBJECT POLICY_NAME
     -------- -------- ------ ------------------------------
     SQL_TEXT
     -------------------------------------------------------
     MILLER   PIET     EMP    MYPOLICY1
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
     VALUES(1000, 'SAM', 800, 15, 10)

     MILLER   PIET     EMP    MYPOLICY1
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
     VALUES(5000, 'RUDY', 2979, 15, 10)

     MILLER   PIET     EMP    MYPOLICY1
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, DEPTNO)
     VALUES(69, 'MILLER', 800, 1000, 10)

---------------------------------------
1.5 Restrictions on the Audit Condition
---------------------------------------

     ---------------------------
     1.5.1 Simple predicate only
     ---------------------------

          execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');

   
     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
          object_schema   => 'PIET', -
          object_name     => 'EMP', -
          policy_name     => 'mypolicy1', -
          audit_condition => 'sal < 1000 and job=''CLERK''', -
          audit_column    => 'comm', -
          enable          => TRUE,  -
          statement_types => 'INSERT');

     PL/SQL procedure successfully completed.

     SQL> conn miller/miller
     Connected.

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, job, COMM, DEPTNO)
          VALUES(5556, 'RUDY2', 800, 'CLERK', 15, 10);
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, job, COMM, DEPTNO)
                        *
     ERROR at line 1:
     ORA-28138: Error in Policy Predicate

     --> In the trace file :

         FGA supports simple predicates only - error 28138
         FGA Policy MYPOLICY1

     --------------------
     1.5.2. No subqueries
     --------------------

          conn system/manager

          execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');


     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
           object_schema   => 'PIET', -
           object_name     => 'EMP', -
           policy_name     => 'mypolicy1', -
           audit_condition => 'sal > (select comm from piet.emp e where emp.empno=e.empno)', -
           audit_column    => 'hiredate', -
           enable          => TRUE,  -
           statement_types => 'INSERT');

     PL/SQL procedure successfully completed.

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, hiredate, DEPTNO)
           VALUES(5557, 'RUDY3', 800, 1000, '21-NOV-2003', 10);
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, hiredate, DEPTNO);
                                         *
     ERROR at line 1:
     ORA-03113: end-of-file on communication channel


     --> In the trace file :

     ORA-07445: exception encountered: core dump [] [SIGBUS] [unknown code] [0x000000
     0F8] [] []
     Current SQL statement for this session:
     INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, COMM, hiredate, DEPTNO)
      VALUES(5557, 'RUDY3', 800, 1000, '21-NOV-2003', 10)

     ------------------------------------
     1.5.3 No reference to remote objects
     ------------------------------------

---------------------------------------------------------------------------
1.6 The Audit Column can refer --> several columns
                               --> Object Types column
                               --> Hidden OLS column (Oracle Label Security)
---------------------------------------------------------------------------
Example with several audit columns and audit_column_opts => DBMS_FGA.ALL_COLUMNS:

          execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');


     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
          object_schema   => 'PIET', -
          object_name     => 'EMP', -
          policy_name     => 'mypolicy1', -
          audit_condition => 'JOB=''CLERK''', -
          audit_column    => 'sal, comm', -
                    audit_column_opts => DBMS_FGA.ALL_COLUMNS, -
          enable          => TRUE,  -
          statement_types => 'INSERT');

     PL/SQL procedure successfully completed.

     SQL> conn miller/miller
     Connected.

     ---------------------------------------------------------------------
     1.6.1 sal and comm are both present in the INSERT ==> INSERT audited
     ---------------------------------------------------------------------

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, SAL, JOB, COMM, DEPTNO)
          VALUES(1200, 'ALBERT', 2800, 'CLERK', 15, 10);
     1 row created.

     ---------------------------------------------------------------------
     1.6.2 Only sal is present in the INSERT ==> INSERT not audited
     ---------------------------------------------------------------------

     SQL> INSERT INTO PIET.EMP(EMPNO, ENAME, JOB, SAL, DEPTNO)
          VALUES(1230, 'DUPONT', 'CLERK' , 2800, 10);
     1 row created.

     SQL> commit;
     Commit complete.

     SQL> conn / as sysdba
     Connected.
     SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,
                 POLICY_NAME,SQL_TEXT
          from dba_fga_audit_trail ;

     DB_USER  SCHEMA   OBJECT POLICY_NAME
     -------- -------- ------ ------------------------------
     SQL_TEXT
     ---------------------------------------------------------
     MILLER   PIET     EMP    MYPOLICY1
     INSERT INTO PIET.EMP(EMPNO, ENAME, JOB, SAL, COMM, DEPTNO)
     VALUES(1200, 'ALBERT', 'CLERK' , 2800, 15, 10)

     If the audit_column_opts parameter had been left to its default value
     DBMS_FGA.ANY_COLUMNS, the statement above would have been audited, because
     at least one of the audit_column columns is present.


Example with Object Type column :

     SQL> CREATE OR REPLACE TYPE Address AS OBJECT
          ( Street       VARCHAR2(80),
            City         VARCHAR2(80),
            State        CHAR(2),
            Zip          VARCHAR2(10) );
          /

     Type created.

     SQL> CREATE TABLE People
          ( FirstName varchar2(16) NOT NULL,
            LastName  varchar2(16) NOT NULL,
            CurAddr   address DEFAULT Address('500 Oracle Parkway',
                                      'Redwood Shores',
                                      'CA', '94065') );
    
     Table created.

     SQL> INSERT INTO People
          VALUES ( 'James','Ellison', Address('1 First Street', 'Orlando', 'FL', '34812'));

     1 row created.

     SQL> commit;
     Commit complete.

          conn system/manager

     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
           object_schema   => 'PIET', -          
           object_name     => 'PEOPLE', -
           policy_name     => 'mypolicy1', -
           audit_column    => 'CurAddr', -
           enable          => TRUE,  -
           statement_types => 'INSERT');

     PL/SQL procedure successfully completed.

          conn piet/piet

     SQL> grant all on piet.people to miller;
     Grant succeeded.

     SQL> grant execute on address to miller;
     Grant succeeded.

     SQL> connect miller/miller
     Connected.

     ---------------------------------------------------------------------
     1.6.3 CurAddr is present in the INSERT ==> INSERT audited     
     ---------------------------------------------------------------------

     SQL> INSERT INTO piet.People
          VALUES ( 'Henri','Patterson',
                   piet.Address('2 First Street','Orlando', 'FL','34812'));
     1 row created.

     ---------------------------------------------------------------------
     1.6.4 CurAddr is not present in the INSERT ==> INSERT not audited
     ---------------------------------------------------------------------

     SQL> INSERT INTO piet.People (firstname,lastname) VALUES ('Anne','Leger');
     1 row created.

     SQL> Commit;
     Commit complete.

          conn system/manager

     SQL> select DB_USER,OBJECT_SCHEMA "SCHEMA",OBJECT_NAME,
                POLICY_NAME,SQL_TEXT
          from dba_fga_audit_trail ;


     DB_USER  SCHEMA   OBJECT POLICY_NAME
     -------- -------- ------ ------------------------------
     SQL_TEXT
     -------------------------------------------------------
     MILLER   PIET     PEOPLE  MYPOLICY1  
     INSERT INTO piet.People VALUES ( 'Henri','Patterson',
     piet.Address('2 First Street', 'Orlando', 'FL','34812'))   
                           

                            -----------------------------                           
                            B. Behavior of UPDATE and FGA
                            -----------------------------

          execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');


     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
          object_schema   => 'PIET', -
          object_name     => 'EMP', -
          policy_name     => 'mypolicy1', -
          audit_condition => 'sal < 900' ,-
          audit_column    => 'comm' ,-
          enable          => TRUE,  -
          statement_types => 'UPDATE');

     PL/SQL procedure successfully completed.

     SQL> conn miller/miller
     Connected.

-----------------------------------------------------
2.1 comm column is not present ==> UPDATE not audited
-----------------------------------------------------
     SQL> update piet.emp set sal=sal*1.1;
     17 rows updated.

-----------------------------------------------------------------
2.2 sal value does not match the condition ==> UPDATE not audited
-----------------------------------------------------------------
     SQL> update piet.emp set sal=sal*2 where comm < 2000 and sal>1000;
     11 rows updated.

-------------------------------------------------------------------------
2.3 comm is present and sal value matches the condition ==> UPDATE audited
-------------------------------------------------------------------------
     SQL> update piet.emp set comm = 10000 where sal < 900;
     1 row updated.

--------------------------------------------------------------
2.4 comm column is present and sal value matches the condition
    ==> UPDATE audited
--------------------------------------------------------------
     SQL> update piet.emp set comm=1000
       2  where sal<(select max(comm) from piet.emp);

     18 rows updated.

------------------------------------------------------------------------
2.5 The Audit Condition can contain functions: SYSDATE, UID, USER, ROUND
------------------------------------------------------------------------

           conn system/manager

           execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');

   
     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
          object_schema   => 'PIET', -
          object_name     => 'EMP', -
          policy_name     => 'mypolicy1', -
          audit_condition => 'hiredate < sysdate', -
          audit_column    => 'comm', -
          enable          => TRUE,  -
          statement_types => 'UPDATE');

     PL/SQL procedure successfully completed.

     SQL> connect miller/miller
     Connected.

     ------------------------------------------------------------------------------------------
     2.5.1 selection matches the audit condition
           comm column is present
           ==>  UPDATE is audited
     ------------------------------------------------------------------------------------------

     SQL> update piet.emp set comm=800;
     18 rows updated.

     ------------------------------------------------------------------------------------------
     2.5.2 set clause does not match the audit condition
           comm column is present
           ==>  UPDATE is not audited
     ------------------------------------------------------------------------------------------

     SQL> update piet.emp set hiredate=sysdate where hiredate is null ;
     1 row updated.

The same restrictions in the Audit Condition apply for UPDATE statement_type as
stated in 1.5.

---------------------------------------------
2.6 Audit column refers an object type column
---------------------------------------------
     SQL> connect piet/piet
     Connected.
     SQL> drop table piet.people;
     Table dropped.

     SQL> CREATE OR REPLACE TYPE PhoneList AS VARRAY(10) OF NUMBER(2);
       2  /

     Type created.

     SQL> CREATE TABLE People
             ( FirstName varchar2(16) NOT NULL,
               LastName  varchar2(16) NOT NULL,
              PhoneNums    PhoneList );
     Table created.

     SQL> INSERT INTO People (firstname, lastname) VALUES ( 'George','Bren');
     SQL> INSERT INTO People VALUES ( 'James','Ellison',    
            phoneList(1,2,3,4,5,6,7,8,9,10));
     SQL> INSERT INTO People VALUES ( 'Henry','Lon',   
            phoneList(10,20,30,40,50,60,70,80,90,99));
     SQL> commit;
     Commit complete.

           conn system/manager

           execute dbms_fga.drop_policy( object_schema => 'PIET',-
                              object_name => 'EMP',-
                              policy_name => 'mypolicy1');


     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
          object_schema   => 'PIET', -
          object_name     => 'PEOPLE', -
          policy_name     => 'mypolicy1', -
          audit_column    => 'firstname', -
          audit_condition => 'phonenums is not null' , -
          enable          => TRUE,  -
          statement_types => 'UPDATE');

     PL/SQL procedure successfully completed.

     SQL> grant all on piet.people to miller;
     Grant succeeded.
     SQL> grant execute on phonelist to miller;
     Grant succeeded.

     SQL> connect miller/miller
     Connected.

     --------------------------------------------
     2.6.1 firstname column is not present
           phonenums value matches the condition
           ==> UPDATE not audited
     --------------------------------------------

     SQL> UPDATE piet.People set   
          phonenums=piet.phonelist(10,20,30,40,50,60,70,80,90,98) ;
     3 rows updated.

     --------------------------------------------
     2.6.2 firstname column is present
           phonenums value matches the condition
           ==> UPDATE audited
     --------------------------------------------

     SQL> update piet.People set    
          phonenums=piet.phonelist(1,2,3,40,50,60,70,80,90,00)
          where firstname='James';
     1 row updated.

The Audit Column can also refer hidden OLS column as stated in 1.6


                            -----------------------------
                            C. Behavior of DELETE and FGA
                            -----------------------------

DELETE statements are always audited, whichever audit columns are defined,
because all columns in a table are touched by a delete statement.

The same restrictions in the Audit Condition apply for DELETE statement_type as
stated in 1.5.


                            -----------------------------
                            D. Behavior of MERGE and FGA
                            -----------------------------

MERGE statement is not considered as a single auditable statement:
it requires that policies are set up for INSERT and/or UPDATE statements
separetely. Otherwise, if the MERGE performs both INSERTs and UPDATEs, only one
record is registered according to the STATEMENT_TYPE declared in the policy.

--------------------------------------------------------------
Case with 2 policies created
--------------------------------------------------------------
     SQL> connect / as sysdba
     Connected.
     SQL> delete from sys.fga_log$;
     2 rows deleted.

     SQL> CREATE TABLE MERGE1 (CODI VARCHAR2(3),ESTAT VARCHAR2(1),TAR_ECO VARCHAR2(1;
     Table created.

     SQL> CREATE TABLE MERGE2 (CODI VARCHAR2(3), ESTAT VARCHAR2(1), TAR_ECO VARCHAR2;
     Table created.

     SQL> INSERT INTO MERGE1 VALUES(100, 5, 002);
     1 row created.

     SQL> INSERT INTO MERGE1 VALUES(101, 5, 001);
     1 row created.

     SQL> INSERT INTO MERGE1 VALUES(102, 5, 001);
     1 row created.

     SQL> INSERT INTO MERGE1 VALUES(117, 5, 001);
     1 row created.

     SQL> INSERT INTO MERGE2 VALUES(100, 1, 001);
     1 row created.

     SQL> grant all on merge1 to miller;
     Grant succeeded.

     SQL> grant all on merge2 to miller;
     Grant succeeded.
 
     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
     >   object_schema   =>'SCOTT', -
     >   object_name     =>'MERGE2', -
     >   policy_name     =>'P_U', -
     >   audit_condition =>'MERGE2.CODI>100' , -
     >   audit_column    =>'tar_eco', -
     >   enable          => TRUE,  -
     >   statement_types =>'UPDATE');

     PL/SQL procedure successfully completed.

     SQL> execute sys.DBMS_FGA.ADD_POLICY(-
     >   object_schema   =>'SCOTT', -
     >   object_name     =>'MERGE2', -
     >   policy_name     =>'P_I', -
     >   audit_condition =>'1=1' , -
     >   audit_column    =>'tar_eco', -
     >   enable          => TRUE,  -
     >   statement_types =>'INSERT');

     PL/SQL procedure successfully completed.

     SQL> select * from scott.merge2;

     COD E TAR_ECO
     --- - ----------
     100 1 1

     SQL> connect miller/miller
     Connected.
     SQL> merge into scott.merge2  d
       2  using (select * from scott.merge1) s on (d.codi=s.codi)
       3      when matched
       4         then update set d.estat=s.estat
       5      when not matched
       6         then insert values (S.CODI,s.estat,s.tar_eco);

     4 rows merged.

     SQL> select * from scott.merge2;

     COD E TAR_ECO
     --- - ----------
     100 5 1
     102 5 1
     117 5 1
     101 5 1

     SQL> connect system/manager
     Connected.
     SQL> select policy_name,object_name, statement_type, sql_text
       2  from DBA_FGA_AUDIT_TRAIL;

     POLICY_NAME OBJECT_NAME STATEM SQL_TEXT
     ----------- ----------- ------ ------------------------------------------
     P_I         MERGE2      UPDATE merge into scott.merge2  d
                                    using (select * from scott.merge1) s on (d.codi=s.codi)
                                    when matched
                                      then update set d.estat=s.estat
                                    when not matched
                                      then insert values (S.CODI,s.estat,s.tar_eco)

     P_U         MERGE2      INSERT merge into scott.merge2  d
                                    using (select * from scott.merge1) s on (d.codi=s.codi)
                                    when matched
                                      then update set d.estat=s.estat
                                    when not matched
                                      then insert values (S.CODI,s.estat,s.tar_eco)

     2 rows are recorded for the MERGE statement since 2 DML operations are
     executed: INSERT and UPDATE.
     (There is an unpublished bug related to the wrong statement displayed
      in the statement_type column).
   
--------------------------------------------------------------
Same case with only 1 policy created (P_U to audit updates)
--------------------------------------------------------------

     SQL> select policy_name,object_name, statement_type, sql_text
       2  from DBA_FGA_AUDIT_TRAIL;

     POLICY_NAME OBJECT_NAME STATEM SQL_TEXT
     ----------- ----------- ------ ------------------------------------------
     P_U         MERGE2      INSERT merge into scott.merge2  d
                                    using (select * from scott.merge1) s on (d.codi=s.codi)
                                    when matched
                                      then update set d.estat=s.estat
                                    when not matched
                                      then insert values (S.CODI,s.estat,s.tar_eco)

     1 row only is recorded for the MERGE statement though 2 DML operations are
     executed: INSERT and UPDATE.
     (Same unpublished bug related to the wrong statement displayed in the
      statement_type column).

Related Documents
~~~~~~~~~~~~~~~~~
Note 175292.1 Overview Auditing: Possibilities of Auditing, using Triggers and FGA
Note 174556.1 9i/9.2: Fine Grained Auditing
Note 249438.1 10G: New Value DB_EXTENDED for the AUDIT_TRAIL init.ora Parameter
Note 199419.1 How to Avoid Common Flaws and Errors Using Fine Grained Auditing

 

TAG •
  • dbkill 2010.04.11 20:00 Files첨부 (2)

    Oracle9i Database, Oracle Database 10g에서 향상된 감사 기능
     
    Fine-Grained Auditing
     
     
    데이터베이스 감사의 기본적인 기능과 특별히 Oracle9i Database부터 소개된 Fine-Grained Auditing에 대해서 상세히 소개한다. 또한 Oracle Database 10g의 Fine-Grained Auditing에 추가된 기능에 대해서도 살펴보자.
     
    데이터베이스 사용자에게는 일정한 권한이 부여된다. 사용자는 부여 받은 권한으로 데이터를 조작하거나 조회할 수 있다. 이 때 조회(Select), 조작(Delete/Insert/Update) 권한을 부여 받은 사용자들이 그 권한을 과도하게 사용하는 경우가 있다. 즉, 조회 권한으로 개인정보나 급여정보 같은 중요한 데이터를 조회한다거나 조작권한을 임의로 사용하여 데이터를 변경하는 경우이다. 이러한 일들이 발생하는 것을 막기 위해서는 사용자가 데이터를 조회, 조작할 때마다 이에 대한 정보를 기록하여, 누가, 언제, 무엇을 했는지 확인할 방법이 필요하다. 이것을 ‘감사(Audit)’라 한다.
     
    감사 기능은 크게 다음과 같이 3가지 형태로 나눌 수 있다.
     
    • Audited by default
    - Instance startup and instance shutdown
    - Administrator privileges

    • Database auditing(or Standard Auditing
    - Enabled by the DBA
    - Cannot record column values

    • Value-based or application auditing
    - Implemented through code
    - Can record column values
    - Used to track changes to tables
     
    데이터베이스감사
     
    FGA(Fine-Grained Auditing)에 대해서 설명하기 전에 일반적인 데이터베이스 감사(Database Auditing)에 대해서 먼저 알아보자. 데이터베이스 감사 기능은 데이터베이스 관리자인DBA가 활성화/비활성화할 수 있다. 이 감사 기능은 <그림 1>에서 볼 수 있듯이 몇 단계의 절차를 따르게 된다.
     
    1. Enable/Disable Database Auditing
    파라미터중 AUDIT_TRAIL을 이용하여 데이터베이스 감사 기능에 대한 활성화/비활성화를 지정한다.
     
    AUDIT_TRAIL = value
     
    다음 중 하나를 값으로 사용할 수 있다.
     
    • TRUE 또는 DB : Audit를 활성화하고 모든 Audit 결과는 SYS.AUD$에 저장한다.
    • DB_EXTENDED(Oracle Database 10g) : Oracle Database 10g부터 가능한 값으로, SYS.AUD$에 저장되는 Audit Trail 정보 중에 SQLBIND, SQLTEXT 컬럼 정보가 추가로 생성되는 감사 기능 활성화 설정이다.
    • OS : 운영체제에서 허용하는 경우 Audit를 활성화하고, 모든 Audit 레코드를 운영체제 파일로 저장한다.
    • FALSE 또는 NONE : Audit를 비활성화한다.
     
    2. Specifying Audit Options
    AUDIT 명령어를 이용하여 설정한다. NOAUDIT 명령어는 설정에 대한 해제 시 사용한다.
     
    • Statement Auditing : AUDIT TABLE;
    • Privilege Auditing : AUDIT create any trigger;
    • Schema Object Auditing : AUDIT select on hr.employees;
     
    3. Execute Command
    감사 기능이 지정된 명령어를 사용자가 수행한다.
     
    4. Generate Audit Trail
    Audit Trail이 SYS.AUD$에 생성된다.
     
    5. Review Audit Information
    Audit에 설정과 Audit Trail 정보를 딕셔너리 뷰를 통해 조회한다.
     
     1.JPG
     
    예제1 : 데이터베이스 감사
     
    다음은 일반적인 데이터베이스 감사 기능에 대한 예제이다.
     
    AUDIT_TRAIL=NONE으로 설정된 경우
     
    OS> sqlplus ‘/as sysdba’
    SQL> show parameter audit_trail
    NAME TYPE VALUE
    --------------------------- ----------- ---------------------
    audit_trail string NONE
    SQL> audit select on hr.employees;
    Audit succeeded.
    SQL> select owner, object_name, object_type, sel from dba_obj_audit_opts
    2 where object_name = ‘EMPLOYEES’;
    OWNER OBJECT_NAME OBJECT_TY SEL
    ------------------------------ ------------------------------
    HR EMPLOYEES TABLE S/S
    SQL> conn hr/hr
    Connected.
    SQL> select * from employees where department_id = 10;
    EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
    HIRE_DATE JOB_ID
    ------------------------- -------------------- --------- --
    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
    -------------- ---------- ---------------------------------
    200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-87 AD_ASST
    4400 101 10
    SQL> conn /as sysdba
    Connected.
    SQL> select count(*) from sys.aud$;
    COUNT(*)
    ----------
    0
    SQL> select username, owner, obj_name, action_name, ses_actions,
    2 decode(returncode,’0’,’success’,returncode) Sess,
    3 to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’)
    4 from dba_audit_object;
    no rows selected
     
    AUDIT_TRAIL 파라미터를 DB, OS, DB_EXTENDED(Oracle Database 10g) 로 설정하지 않으면 감사 기능은 비활성화 상태이다.
     
    AUDIT_TRAIL=DB로 설정된 경우
     
    SQL> show parameter audit_trail
    NAME TYPE VALUE
    ------------------------------------ ----------- ------------
    audit_trail string DB
    SQL>
    SQL> select owner, object_name, object_type, sel from dba_obj_audit_opts
    2 where object_name = ‘EMPLOYEES’;
    OWNER OBJECT_NAME OBJECT_TY SEL
    ------------------------------ ------------------------------
    HR EMPLOYEES TABLE S/S
    SQL> select count(*) from sys.aud$;
    COUNT(*)
    ----------
    0
    SQL> conn hr/hr
    Connected.
    SQL> select * from employees where department_id = 10;
    EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
    HIRE_DATE JOB_ID
    ------------------------- -------------------- --------- -------
    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
    ------------- ---------- ---------------------------------------
    200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-87
    AD_ASST
    4400 101 10
    SQL> conn /as sysdba
    Connected.
    SQL> select count(*) from sys.aud$;
    COUNT(*)
    ----------
    1
    SQL> select username,obj_name, action_name, ses_actions,
    2 decode(returncode,’0’,’success’,returncode) Sess,
    3 to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’)
    4 from dba_audit_object;
    USERNAME OBJ_NAME ACTION_NAME SES_ACTIONS SESS
    TO_CHAR(TIMESTAMP,’D
    -------- ---------- ------------ -------------------- ---------
    --------------------
    HR EMPLOYEES SESSION REC ----S----- success 16-AUG-
    2005 14:09:10
     
    Audit Trail이 정상적으로 생성되었으나 위의 결과를 볼 때 과연 어떤 SELECT문을 실행해서 감사가 되었는지 정확히 알 수 없다. 또한 조회된 데이터가 무엇인지 알 수도 없다.
     
    SQL> noaudit select on hr.employees;
    Noaudit succeeded.
    SQL> select owner, object_name, object_type, sel from dba_obj_audit_opts
    2 where object_name = ‘EMPLOYEES’;
    OWNER OBJECT_NAME OBJECT_TY SEL
    ---------- ------------------------------ --------- ---
    HR EMPLOYEES TABLE -/-
     
    AUDIT_TRAIL=DB_EXTENDED로 설정된 경우, Oracle Database 10g 의 새로운 설정
     
    SQL> show parameter audit_trail
    NAME TYPE VALUE
    ----------------------------- ----------- ----------------
    audit_trail string DB_EXTENDED
    SQL> create table hr.test
    2 ( col1 number(2), col2 varchar2(14), col3 varchar2(14));
    Table created.
    SQL> insert into hr.test values(10,’value1’,’test1’);
    1 row created.
    SQL> insert into hr.test values(20,’value2’,’test2’);
    1 row created.
    SQL> commit;
    Commit complete.
    SQL> select * from hr.test;
    COL1 COL2 COL3
    ---------- -------------- --------------
    10 value1 test1
    20 value2 test2
    SQL> create or replace procedure hr.ins_test
    2 (p_col1 in hr.test.col1%type,
    3 p_col2 in hr.test.col2%type,
    4 p_col3 in hr.test.col3%type) is
    5
    6 stmt_str varchar2(200);
    7 rows_processed number;
    8 cur_handle number;
    9
    10 begin
    11 stmt_str := ‘INSERT INTO hr.test VALUES(:p_col1,:p_col2,:p_col3)’;
    12 cur_handle := DBMS_SQL.OPEN_CURSOR;
    13 DBMS_SQL.PARSE(cur_handle, stmt_str,DBMS_SQL.NATIVE);
    14 DBMS_SQL.BIND_VARIABLE(cur_handle, ‘:p_col1’, p_col1);
    15 DBMS_SQL.BIND_VARIABLE(cur_handle, ‘:p_col2’, p_col2);
    16 DBMS_SQL.BIND_VARIABLE(cur_handle, ‘:p_col3’, p_col3);
    17
    18 rows_processed := DBMS_SQL.EXECUTE(cur_handle);
    19
    20 DBMS_SQL.CLOSE_CURSOR(cur_handle);
    21 END;
    22 /
    Procedure created.
    SQL> audit insert on hr.test by access;
    Audit succeeded.
    SQL> select owner, object_name, object_type, ins from dba_obj_audit_opts
    2 where object_name = ‘TEST’ and owner =’HR’
    3 /
    OWNER OBJECT_NAME OBJECT_TY INS
    -------- ------------------------------ --------- ---
    HR TEST TABLE A/A
    SQL> select username, owner, obj_name, action_name, ses_actions,
    2 decode(returncode,’0’,’success’,returncode) Sess,
    3 to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’)
    4 ,sql_text, sql_bind
    5 from dba_audit_object
    6 /
    no rows selected
    SQL> conn hr/hr
    Connected.
    SQL> exec ins_test(30,’value3’,’test3’);
    PL/SQL procedure successfully completed.
    SQL> select * from test;
    COL1 COL2 COL3
    ---------- -------------- --------------
    10 value1 test1
    20 value2 test2
    30 value3 test3
    SQL> conn /as sysdba
    Connected.
    SQL> select username, owner, obj_name, action_name, ses_actions,
    2 decode(returncode,’0’,’success’,returncode) Sess,
    3 to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’)
    4 ,sql_text, sql_bind
    5 from dba_audit_object
    6 /
    USERNAME OWNER OBJ_NAME ACTION_NAM SES_ACTION SESS
    -------- -------- ---------- ---------- ---------- ----------
    TO_CHAR(TIMESTAMP,’D
    ----------------------------
    SQL_TEXT
    ---------------
    SQL_BIND
    ----------------
    HR HR TEST INSERT success
    18-AUG-2005 10:12:18
    INSERT INTO hr.test VALUES(:p_col1,:p_col2,:p_col3)
    #1(2):30 #2(6):value3 #3(5):test3
    SQL> noaudit insert on hr.test;
    Noaudit succeeded.
    SQL> drop table hr.test;
    Table dropped.
    SQL> truncate table aud$;
    Table truncated.
     
    Fine-Grained Auditing
     
    앞의 예제에서 보았듯이 데이터베이스 감사 기능을 HR 스키마 내의 EMPLOYEES 테이블에 대한 조회시 설정했을 때 특정 사용자가 HR. EMPLOYEES를 조회하는 경우, 이에 대한 정보가 Audit Trail의 형태로 저장된다. 이 때 HR.EMPLOYEES 테이블의 모든 컬럼, 모든 데이터가 다 중요한 데이터는 아닐 것이다.
     
    데이터베이스 감사 기능을 사용하는 경우는 데이터의 경중을 따지지 않고 HR.EMPLOYEES 테이블에 대한 SELECT를 하는 모든 경우에 대해서 Audit Trail이 생성된다. 따라서 Audit Trail의 양은 아주 많을 것 이다. 이러한 Audit Trail 중에서 HR.EMPLOYEES 테이블의 데이터 관점에서 중요한 급여 데이터나 특정 부서 데이터가 조회된 경우를 찾기 는 어려울 것이다. 또한 직접적으로 사용된 SELECT문을 알아내기도 어렵다.
     
    이러한 문제점을 극복하기 위해서 등장한 것이 Oracle9i Database의 FGA(Fine-Grained Auditing)이다. 이 기능은 특정 데이터를 조회하는 경우에만 감사가 활성화되도록 설정하는 것이다.
     
    FGA의 특징은 다음과 같다.
     
    • 더 상세한 레벨의 감사 기능을 제공한다. 선택적 감사를 위한 조건으로 사용자가 정의한 SQL문 술어를 기반으로 한다.
    • 값에 의한 감사뿐 아니라 특정 컬럼이 참조 또는 액세스 되었는지 여부에 대해 감사를 지정할 수 있다.
    • DBMS_FGA 패키지를 이용하여 FGA 기능을 활성화/비활성화 한다.
    • CBO(Cost Based Optimizer)를 사용하는 경우에 정상적으로 작동한다. 인스턴스 레벨로 CBO 모드가 설정되어 있어야 하며, SQL문에 힌트가 없어야 하며, 최소한 액세스되는 테이블에 대한 분석이 수행되어 있어야 한다.
    • Oracle9i Database는 SELECT문에 대해서만 가능하다.
    • Oracle Database 10g는 SELECT 외에 INSERT, UPDATE, DELETE, MERGE 문에 대해서도 가능하다.
    • Oracle Database 10g에서는 컬럼에 대한 지정 옵션으로 다음을 사용할 수 있다.
     
    DBMS_FGA.ALL_COLUMNS/DBMS_FGA.ANY_COLUMNS
     
    • 일반적인 데이터베이스 감사 설정시 Audit Trail은 SYS.AUD$에 저장되며, FGA의 경우는 SYS.FGA_LOG$에 저장된다.
     
    DBMS_FGA 패키지 및 딕셔너리 뷰
     
    Oracle9i Database의 DBMS_FGA 패키지의 사양은 다음과 같다.
     
    DBMS_FGA.ADD_POLICY(
    object_schema VARCHAR2,   ----- ①
    object_name VARCHAR2,     ----- ②
    policy_name VARCHAR2,     ----- ③
    audit_condition VARCHAR2, ----- ④
    audit_column VARCHAR2,    ----- ⑤
    handler_schema VARCHAR2,  ----- ⑥
    handler_module VARCHAR2,  ----- ⑦
    enable BOOLEAN );         ----- ⑧
    DBMS_FGA.DROP_POLICY(
    object_schema VARCHAR2,   ----- ①
    object_name VARCHAR2,     ----- ②
    policy_name VARCHAR2 );   ----- ③
     
    Oracle Database 10g의 경우는 ADD_POLICY 프로시저는 다음과 같으며, ⑨, ⑩, ⑪ 항목이 Oracle Database 10g에 새롭게 추가된 부분이다.
     
    DBMS_FGA.ADD_POLICY(
    object_schema VARCHAR2,                       ----- ①
    object_name VARCHAR2,                         ----- ②
    policy_name VARCHAR2,                         ----- ③
    audit_condition VARCHAR2,                     ----- ④
    audit_column VARCHAR2                         ----- ⑤
    handler_schema VARCHAR2,                      ----- ⑥
    handler_module VARCHAR2,                      ----- ⑦
    enable BOOLEAN,                               ----- ⑧
    statement_types VARCHAR2,                     ----- ⑨
    audit_trail BINARY_INTEGER IN DEFAULT,        ----- ⑩
    audit_column_opts BINARY_INTEGER IN DEFAULT); ----- ⑪
     
    ①, ②, ③은 Fine-Grained Audit를 설정하고자 하는 스키마명, 오브젝트명(테이블, 뷰 명), Policy명을 지정한다. 이 때 Policy명은 유니 크해야 한다.
     
    ④는 상세한 감사 기능을 지정하는 것으로, 스키마 오브젝트에 대한 논리적인 데이터 그룹에 상응하는 WHERE절을 지정한다. 이 때 AUDIT_CONDITION=>'department_id=10'을 지정한 경우, SQL 문에 명시적으로 해당 조건절이 나오는 경우뿐만 아니라, SQL문에 기술된 다른 조건에 의해서도 AUDIT_CONTION에 기술된 데이터 그룹에 대한 조건을 만족하는 경우에도 Audit Trail은 생성된다.
     
    SQL문에서 사용되는 조건절이 기술된 AUDTION_CONDITION 과 명시적, 암시적으로 동일한 모든 경우에 Audit Trail이 생성된다. 즉, SELECT ... FROM employees; 문장처럼 특별한 조건 없이 전체를 조회하는 경우에도 AUDIT_CONDITION에 지정한 department_id=10 인 데이터 또한 조회되므로, 해당 문장도 FGA의 대상이 된다. 이에 대해 서는 다음의‘예제 3 : Oracle9i Database FGA 예제 2’에서 좀 더 자세히 다룰 것이다.
     
    ⑤ 감사 대상이 되는 컬럼명을 지정한다. Oracle9i Database까지 는 한 개의 컬럼명만을 지정할 수 있으나 Oracle Database 10g부터는 하나 이상의 컬럼을 지정할 수 있다. 또한 Oracle Database 10g에서는 ⑪ 옵션을 이용하여 AUDIT_COLUMN에 대한 추가 설정이 가능하다.
     
    ⑥, ⑦ FGA 설정에 의해서 Audit Trail이 생성될 때 같이 실행될 수 있는 프로시저를 작성하는 경우, 프로시저의 스키마명과 프로시저명을 기술한다. 이 때 생성되는 프로시저는 다음의 패턴을 따른다.
     
    PROCEDURE <fname> ( object_schema VARCHAR2, object_name VARCHAR2,
    policy_name VARCHAR2 ) AS ...
     
    ⑧ FGA 기능의 활성화 여부를 지정한다. 디폴트 값이 TRUE임에도 불구하고, 명시적으로 TRUE를 지정하지 않으면 FGA 설정이 활성화되지 않는다.
     
    ⑨ Oracle Database 10g에 추가된 기능으로 FGA가 적용되는 문장 종류에 대해서 기술한다. INSERT, UPDATE, DELETE, SELECT 중에서 지정한다. MERGE문은 내부적으로 INSERT 또는 UPDATE의 지정의 영향을 받는다. 기본값은 SELECT이다.
     
    ⑩ Oracle Database 10g에 추가된 기능으로‘AUDIT_TRAIL=>DBMS_ FGA.DB_EXTENDED’로 설정하는 경우 SYS.FGA_LOG$ 의 LSQLTEXT, LSQLBIND컬럼에 Audit Trail이 생성된다.
     
    ⑪ Oracle Database 10g에 추가된 기능으로 AUDIT_COLUMN에 나열된 컬럼 중에서 모두 액세스될 때 Aduit Trail을 생성하도록 지정하는 DBMS_FGA.ALL_COLUMNS 옵션과 AUDIT_COLUMN에 나열된 컬럼 중에서 하나라도 액세스될 때 AUDIT TRAIL이 생성되도록 지정하는 DBMS_FGA.ANY_COLUMNS 옵션이 있다.
     
    FGA와 관련된 딕셔너리 뷰는 <표 1>과 같다.
     
     2.JPG
     
    FGA 예제들
     
    다음은 샘플 스키마 HR 내의 오브젝트를 대상으로 한 예제이다. 정확한 결과 도출을 위해서 한 예제가 끝날 때마다 SYS.FGA_LOG$ 테이블은 TRUNCATE를 수행하고 이전 설정은 모두 삭제하여 테스트한다.
     
    예제 2 : Oracle9i Database FGA 예제 1
     
    SQL>conn /as sysdba
    Connected
    SQL> show parameter audit_trail
    NAME                                     TYPE       VALUE
    ------------------------------------ ----------- ------------
    audit_trail                             string       NONE
    데이터베이스 감사 기능은 비활성화 상태이다.
    SQL> begin
    2  dbms_fga.add_policy(object_schema=>’HR’,
    3                 object_name=>’EMPLOYEES’,
    4                 policy_name=>’POL2’,
    5                 audit_column=>’EMPLOYEE_ID’,
    6                 enable=>TRUE);
    7  end;
    8  /
    PL/SQL procedure successfully completed.
     
    AUDIT_CONDITION 없이 설정한 경우이고, AUDIT_COLUMN을 지정하지 않으면 해당 오브젝트의 전체 컬럼이 감사 대상이 된다.
     
    SQL> select object_schema, object_name, policy_name,
    2 policy_text, policy_column, enabled
    3 from dba_audit_policies;
    OBJECT_SCHEMA OBJECT_NAME POLICY_NAM POLICY_TEXT
    POLICY_COLUMN ENA
    ---------------------------------- ----------------------------------
    HR            EMPLOYEES         POL2 1=1       EMPLOYEE_ID       YES
     
    설정 정보 중에 POLICY_TEXT는 1=1로 자동으로 지정됨을 확인할 수 있다.
     
    CASE 1 : select * from employees;
    CASE 2 : select salary from employees where department_id = 20;
    CASE 3 : select employee_id from employees where
    department_id = 20;
     
    CASE 2는 AUDIT_COLUMN에 나열된 EMPLOYEE_ID가 조회되지 않았다.
     
    위의 문장들을 HR 스키마 내에서 실행한 후 DBA_FGA_AUDIT_TRAIL을 조회한 결과이다.
     
    SQL>select SESSION_ID,TIMESTAMP,OBJECT_SCHEMA,OBJECT_
    NAME,SCN,SQL_TEXT,SQL_BIND
    2 from dba_fga_audit_trail ;
    SESSION_ID TIMESTAMP OBJECT_SCHEM OBJECT_NAME SCN
    ------------------------------ ---------------------------------
    SQL_TEXT SQL_BIND
    ----------------------------------------------------

           70 18-AUG-05 HR     EMPLOYEES      156000
    select * from employees                   Case 1의 경우, BIND 정보는 없다.

           70 18-AUG-05 HR     EMPLOYEES      156083
    select employee_id from                   Case 3의 경우, BIND 정보는 없다.
    employees where department
    _id = 20
     
    예제 3 : Oracle9i Database FGA 예제 2 - AUDIT_CONDITION
     
    SQL>conn /as sysdba
    Connected
    SQL> show parameter audit_trail
     
    NAME            TYPE                     VALUE
    ---------------------------------------------------
    audit_trail     string                    NONE
     
    데이터베이스 감사 기능은 비활성화 상태이다.
     
    SQL> begin
    2 dbms_fga.add_policy(object_schema=>’HR’,
    3          object_name=>’EMPLOYEES’,
    4          policy_name=>’POL3’,
    5 audit_column=>’EMPLOYEE_ID’,
    7    audit_condition=>’upper(last_name) =’’SMITH’’ and salary >7500’,
    8    enable=>TRUE);
    9 end;
    10 /
    PL/SQL procedure successfully completed.
     
    SQL> select object_schema, object_name, policy_name,
    2    policy_text, policy_column, enabled
    3 from dba_audit_policies
    4 /
     
    OBJECT_SCHEM             OBJECT_NAME                  POLICY_NAM
    ------------------------------------------------------------------
    POLICY_TEXT                      POLICY_COLUMN               ENA
    ------------------------------------------------------------------
    HR              EMPLOYEES     POL3
    upper(last_name) =’SMITH’ and salary > 7500 EMPLOYEE_ID    YES
     
    HR.EMPLOYEES 테이블에는 LAST_NAME이 SMITH이고, SALARY가 8000, DEPARTMENT_ID=80인 로우가 1건 존재한다.
     
    CASE 1 : select employee_id, last_name, salary from employees where
             department_id = 80;
    CASE 2 : select salary, last_name from employees where upper(last_name) =
            ‘SMITH’;
    CASE 3 : select employee_id, last_name , department_name
             from employees e, departments d
             where e.department_id = d.department_id
             and e.salary > 7000
             and e.department_id > 60;
     
    CASE 1의 경우는 department_id = 80인 데이터 중 AUDIT_CONDITION을 만족하는 데이터가 존재한다.
    CASE 2의 경우는 조건은 만족하나 AUDIT_COLUMN을 만족하지 못한다.
    CASE 3의 경우는 명시적인 조건은 만족하지 않지만 조회대상 데이터에 AUDITION_CONDITION을 만족하는 데이터가 존재한다.
     
    SQL>select SESSION_ID,TIMESTAMP,OBJECT_SCHEMA,OBJECT_
    NAME,SCN,SQL_TEXT,SQL_BIND
    2 from dba_fga_audit_trail;
     
    SESSION_ID    TIMESTAMP    OBJECT_SCHEM    OBJECT_NAME     SCN
    ----------------------------------------------------------------
    SQL_TEXT           SQL_BIND
    ------------------------------

          71 18-AUG-05 HR   EMPLOYEES   156647
    select employee_id, last_name,      Case 1의 경우, BIND 정보는 없다.
    salary from employees where
    department_id = 80;

          71 18-AUG-05 HR   EMPLOYEES   156721
    select employee_id, last_name ,     Case 3의 경우, BIND 정보는 없다.
    department_name
    from employees e, departments d
    where e.department_id =
    d.department_id
    and e.salary > 7000
    and e.department_id > 60;
     
    예제 4 : Oracle Database 10g FGA 예제 3 - STATEMENT_TYPE, AUDIT_TRAIL
     
    HR 스키마 내에 TEST 테이블을 생성하고 HR.TEST 테이블에 대해서 FGA를 설정한다.
     
    SQL> conn /as sysdba
    Connected.
    SQL> show parameter audit_trail
     
    NAME            TYPE       VALUE
    ----------------------------------
    audit_trail    string       NONE
     
    SQL> create table hr.test
    2 ( col1 number(2), col2 varchar2(14), col3 varchar2(14));
     
    Table created.
    SQL> begin
    2 dbms_fga.add_policy(object_schema=>’HR’,
    3                     object_name=>’TEST’,
    4                     policy_name=>’POL4’,
    5                     audit_condition=>’col1 > 30’,
    6                     enable=>TRUE,
    7                     statement_types=>’INSERT,SELECT’,
    8
    audit_trail=>DBMS_FGA.DB_EXTENDED);
    9 end;
    10 /
    PL/SQL procedure successfully completed.
     
    HR.TEST의 COL3 컬럼의 데이터가 30 이상이고 INSERT, SELECT문에 대해서 Audit Trail이 생성되도록 지정한다.
     
    SQL> select object_schema, object_name, policy_name,
    2 policy_text, policy_column, enabled
    3 from dba_audit_policies;
     
    OBJECT_S OBJECT_N POLICY_N POLICY_TEXT POLICY_COL ENA
    -----------------------------------------------------------------
    HR     TEST     POL4    col1  > 30      YES
     
    SQL> select TIMESTAMP,OBJECT_NAME,SCN,SQL_TEXT,SQL_BIND
    2 from dba_fga_audit_trail;
    no rows selected

    CASE 1 : insert into test values(:p_col1,:p_col2,:p_col3);
             var p_col1 number
             var p_col2 varchar2(20)
             var p_col3 varchar2(20)
             exec :p_col1 := 40;
             exec :p_col2 := ‘value4’;
             exec :p_col3 := ‘test4’;
    insert into test values(:p_col1,:p_col2,:p_col3);
    CASE 2 : select * from test;
    CASE 3 : insert into test values(50,’value5’,’test5’);
    CASE 4 : delete from test where col1 = 40;
     
    CASE 1은 바인드 변수를 사용하여 INSERT한다.
    CASE 2는 현재 데이터를 조회한다.
    CASE 3은 일반적인 INSERT문을 수행한다.
    CASE 4는 AUDIT_CONDITION에 해당하는 데이터를 DELETE한다. 그러나 DELETE문은 Audit Trail이 생성되지 않는다.
     
    SQL> select to_char(TIMESTAMP,’yyyy/mm/dd
    HH24:MI:SS’),OBJECT_NAME,SCN,
    2 SQL_TEXT,SQL_BIND
    3 from dba_fga_audit_trail;
    TO_CHAR(TIMESTAMP,’ OBJECT_N SCN
    ------------------- -------- ------------------------
    SQL_TEXT
    -----------------------------------------------------
    SQL_BIND
    -------------

    2005/08/18 14:21:02 TEST   0
    insert into test values    Case 1의 경우, Oracle Databae 10g부터
    (:p_col1,:p_col2,:p_col3)  설정에 따라 BIND 정보 존재
    #1(2):40 #2(6):value4 #3(5):test4

    2005/08/18 14:21:13 TEST    713499
    select * from test             Case 2의 경우, BIND 정보는 없다.

    2005/08/18 14:22:22 TEST   0
    insert into test values(50,’value5’,’test5’) Case 3의 경우, BIND 정보는 없다.

    SQL> select count(*) from sys.aud$;
    COUNT(*)
    ----------
    0
     
    비 교 : DBMS_FAG.ADD_POLICY(...AUDIT_TRAIL=>DBMS_FGA.DB_EXTENDED..) 설정은 파라미터 AUDIT_TRAIL=DB_EXTENDED 설정의 의미가 아니다.
     
    예제 5 : Oracle Database 10g FGA 예제4 - AUDIT_COLUMN_OPTS
     
    SQL> conn /as sysdba
    Connected.
    SQL> begin
    2    dbms_fga.add_policy(object_schema=>’HR’,
    3                   object_name=>’EMPLOYEES’,
    4                   policy_name=>’POL5’,
    5                   audit_column=>’employee_id,salary’,
    6                   enable=>TRUE,
    7
    audit_column_opts=>DBMS_FGA.ALL_COLUMNS);
    8 end;
    9 /
     
    PL/SQL procedure successfully completed.
     
    HR.EMPLOYEES 테이블의 EMPLOYEE_ID, SALARY 컬럼 두 개를 동시에 조회하는 경우에 Audit Trail이 생성되도록 설정한다.
     
    SQL> begin
    2    dbms_fga.add_policy(object_schema=>’HR’,
    3                   object_name=>’EMPLOYEES’,
    4                   policy_name=>’POL5’,
    5                   audit_column=>’employee_id,salary’,
    6                   enable=>TRUE,
    7
    audit_column_opts=>DBMS_FGA.ALL_COLUMNS);
    8 end;
    9 /
     
    PL/SQL procedure successfully completed.

    CASE 1 : select employee_id, first_name from employees where department_id = 20;
    CASE 2 : select employee_id, salary , first_name from employees where
             department_id = 20;
    CASE 3 : select salary from employees where department_id = 10;
     
    CASE 1, 3의 경우는 AUDIT_COLUMN에 지정된 두 개의 컬럼 중 하나를 조회하는 경우이므로 Audit Trail이 생성되지 않는다.
     
    SQL> select to_char(TIMESTAMP,’yyyy/mm/dd HH24:MI:SS’),OBJECT_NAME,SCN,
    2    SQL_TEXT,SQL_BIND
    3 from dba_fga_audit_trail
    4 /
     
    TO_CHAR(TIMESTAMP,’ OBJECT_NAM        SCN
    ------------------- ---------- -------------------------
    SQL_TEXT
    --------------
    SQL_BIND
    --------------
    2005/08/18 14:56:10 EMPLOYEES       714563
    select employee_id, salary , first_ Case2 지정된모든컬럼을조회하는경우
    name from employees
    where department_id = 20
     
    SQL> select audit_type, object_schema, object_name, policy_name, sql_text
    2 from dba_common_audit_trail
    3 /
     
    AUDIT_TYPE    OBJECT_S    OBJECT_NAM    POLICY_N
    --------------------------------------------------
    SQL_TEXT
    ----------
    Fine Grained Audit    HR EMPLOYEES POL5
    select employee_id, salary , first_name from employees
    where department_id = 20
     
    Oracle Database 10g에 추가된 DBA_COMMON_AUDIT_TRAIL 뷰는 데이터베이스 감사, FGA에 대한 정보를 모두 제공한다.
     
    실제 수행된 SQL문에 대한 파악 용이
     
    이상에서 살펴보았듯이 Oracle9i Database부터 제공되는 FGA(Fine-Grained Auditing)는 생성되는 Audit Trail의 양은 작으면서 프로그램에서 사용된 SQL문에 대한 정보와 Oracle Database 10g부터 제공되는 BIND 정보 같은 유용한 정보를 추가로 제공하여 실제로 수행된 SQL문에 대한 파악을 용이하게 한다.
    끝으로 이 글이 FGA의 다양한 기능을 익히고 업무에 적극 활용하는 기회가 되기를 바란다.


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84839
76 Tip 오라클 null값 정리 유주환 2010.04.18 29779
75 Tip sysaux resize 유주환 2010.04.18 25269
74 Tip Analyze 통계정보 dbkill 2010.04.11 13268
73 Tip Expdp/Impdp시 ORA-31633 에러 해결 방법 2 김준호 2010.04.20 22368
72 Tip DATAFILE SIZE 줄이는 계산 1 고구마 2010.04.19 13467
71 Tip 세마포어에 대하여 고구마 2010.04.19 13218
70 Tip DML_DDL_로그기록_체크 유주환 2010.04.19 13342
69 Tip 통계 백업 및 생성 유주환 2010.04.18 16172
68 Tip shared pool / library cache 고구마 2010.04.28 25066
67 Tip DML 문의 처리과정 고구마 2010.04.28 17301
66 Tip Buffer Cahe 관련 대기 이벤트들 고구마 2010.04.28 25621
65 Tip Buffer busy waits 고구마 2010.04.28 19587
64 Tip 버퍼캐시와 OWI 1 고구마 2010.04.28 19859
63 Tip IDLE EVENT 유주환 2010.04.23 21485
62 Tip 권한주기 고구마 2010.04.23 15714
61 Tip 핫백업 디비올리기 유주환 2010.04.09 34980
60 Tip partiton table truncate 및 rebuild 작업 유주환 2010.04.09 10545
59 Tip How to Use DBMS_STATS to Move Statistics to a Different Database 고구마 2010.04.09 17030
» Tip 10G FGA AUDIT 1 고구마 2010.04.09 23587
57 Tip EM 재구성 고구마 2010.04.09 26212
Board Pagination Prev 1 2 3 4 Next
/ 4