Blog do Marcio Portes

No artigo anterior, vimos uma necessidade que levou-me a optar por uma solução caseira, ou seja, tive que desenvolver o código para instrumentar as DMLs, valores anteriores e novos para cada tabela.

Vou discutir hoje, uma auditoria built in, o FGA (Fine-grained Audit) espécie de controle de acesso granular. Através da DBMS_FGA podemos cadastrar políticas para determinada tabela e ver o resultado na DBA_FGA_AUDIT_TRAIL.

Primeiro vamos entender a DBMS_FGA e o subprograma que adiciona política a ADD_POLICY.

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);

Parâmetros

object_schema     => Esquema do objeto.
                     Valor default NULL. Se null ele assume o schema corrente

object_name       => Nome do objeto.

policy_name       => Nome único da política

audit_condition   => Indica uma condicional para a auditoria, exemplo: auditar
                     as queries que possuem, na sua cláusula where "sal > 1000". 
                     NULL é o default.

audit_column      => Coluna que será checada por acesso, NULL é default e causará
                     cheque em todas as colunas acessadas.

handler_schema    => Esquema onde está o event handler. Um event hander, pode ser
                     escrito para tomar alguma decisão quando o audit_condition
                     for verdadeiro.

handler_module    => É o módulo (programa) que gerencia a auditoria, geralmente usado
                     como alerta, exemplo, uma procedure que envia e-mail a gerência
                     alertando sobre uma query contra a tabela de ações, por ai vai 
                     conforme a necessidade do negócio. O handler_module deve possuir
                     a seguinte interface:

                     PROCEDURE  ( 
                          object_schema VARCHAR2, 
                          object_name   VARCHAR2, 
                          policy_name   VARCHAR2 )  AS ...

                     Onde fname é o nome da procedure, object_schema é o nome do
                     esquema, object_name nome da tabela que está auditada e 
                     policy_name nome da política.

enable            => true/false - true a política estará ativa. Default TRUE.

statement_types   => Onde haverá auditoria. Valores aceitos: insert, update,
                     delete ou select. Podemos utilizar todos separados por vírgula.
                     Default: SELECT

audit_trail       => Determina se será gravado ou não o sql bind e o sql text na 
                     sys.fga_log$. O default é para que seja populado DB_EXTENDED
                     caso contrário, use DB.


audit_column_opts => Determina se a sentença SQL será audita quando ela acessa
                     QUALQUER (ANY) ou TODAS (ALL) as colunas. Default ANY_COLUMNS.

Partiremos para um exemplo bastante simples, apenas para apresentar o conceito. Será criado um esquema chamado MARCIO e nele criaremos a tabela EMP do SCOTT. Após, faremos a coleta de estatística - a FGA funciona corretamente somente em CBO - e criaremos a política, mais abaixo estarão vários selects com bind variable sem bind, insert e update e logo o resultado na DBA_FGA_AUDIT_TRAIL.

ops$marcio@ORA10G> connect / as sysdba
Connected.
sys@ORA10G> truncate table fga_log$;

Table truncated.

sys@ORA10G> create user marcio identified by marcio default tablespace users;

User created.

sys@ORA10G>
sys@ORA10G> grant connect, resource to marcio;

Grant succeeded.

sys@ORA10G> grant execute on dbms_lock to marcio;

Grant succeeded.

sys@ORA10G> create table marcio.emp as select * from scott.emp;

Table created.

sys@ORA10G>
sys@ORA10G> exec dbms_stats.gather_table_stats( 'MARCIO', 'EMP', cascade => true);

PL/SQL procedure successfully completed.

sys@ORA10G>
sys@ORA10G> begin
  2     dbms_fga.add_policy
  3         ( object_schema   => 'MARCIO',
  4           policy_name     => 'marcio_fga',
  5           object_name     => 'EMP',
  6           statement_types => 'insert, select, update'
  7         );
  8  end;
  9  /

PL/SQL procedure successfully completed.

sys@ORA10G>
sys@ORA10G> exec ops$marcio.print_table('select * from dba_audit_policies');
OBJECT_SCHEMA                 : MARCIO
OBJECT_NAME                   : EMP
POLICY_NAME                   : MARCIO_FGA
POLICY_TEXT                   :
POLICY_COLUMN                 :
PF_SCHEMA                     :
PF_PACKAGE                    :
PF_FUNCTION                   :
ENABLED                       : YES
SEL                           : YES
INS                           : YES
UPD                           : YES
DEL                           : NO
AUDIT_TRAIL                   : DB_EXTENDED
POLICY_COLUMN_OPTIONS         : ANY_COLUMNS
-----------------

PL/SQL procedure successfully completed.

sys@ORA10G>
sys@ORA10G> connect marcio/marcio
Connected.
marcio@ORA10G>
marcio@ORA10G> var x number
marcio@ORA10G> var z varchar2(10)
marcio@ORA10G>
marcio@ORA10G> exec :x := 7900

PL/SQL procedure successfully completed.


            X
-------------
         7900

marcio@ORA10G> exec :z := 'JAMES'

PL/SQL procedure successfully completed.


Z
--------------------------------
JAMES

marcio@ORA10G>
marcio@ORA10G> select * from emp;

        EMPNO ENAME      JOB                 MGR HIREDATE                      SAL          COMM        DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
         7369 SMITH      CLERK              7902 17/12/1980 00:00:00           800                          20
         7499 ALLEN      SALESMAN           7698 20/02/1981 00:00:00          1600           300            30
         7521 WARD       SALESMAN           7698 22/02/1981 00:00:00          1250           500            30
         7566 JONES      MANAGER            7839 02/04/1981 00:00:00          2975                          20
         7654 MARTIN     SALESMAN           7698 28/09/1981 00:00:00          1250          1400            30
         7698 BLAKE      MANAGER            7839 01/05/1981 00:00:00          2850                          30
         7782 CLARK      MANAGER            7839 09/06/1981 00:00:00          2450                          10
         7788 SCOTT      ANALYST            7566 19/04/0087 00:00:00          3000                          20
         7839 KING       PRESIDENT               17/11/1981 00:00:00          5000                          10
         7844 TURNER     SALESMAN           7698 08/09/1981 00:00:00          1500             0            30
         7876 ADAMS      CLERK              7788 23/05/0087 00:00:00          1100                          20
         7900 JAMES      CLERK              7698 03/12/1981 00:00:00           950                          30
         7902 FORD       ANALYST            7566 03/12/1981 00:00:00          3000                          20
         7934 MILLER     CLERK              7782 23/01/1982 00:00:00          1300                          10

14 rows selected.

marcio@ORA10G> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

marcio@ORA10G>
marcio@ORA10G> select * from emp where empno = :x and ename = :z;

        EMPNO ENAME      JOB                 MGR HIREDATE                      SAL          COMM        DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
         7900 JAMES      CLERK              7698 03/12/1981 00:00:00           950                          30

1 row selected.

marcio@ORA10G> exec :x := 7782

PL/SQL procedure successfully completed.


            X
-------------
         7782

marcio@ORA10G> /

no rows selected

marcio@ORA10G> insert into emp values ( 100, 'MARCIO','DBA', 7698, '15/08/2004 00:00:00', 1950, NULL, 30);

1 row created.

marcio@ORA10G> commit;

Commit complete.

marcio@ORA10G> exec :x := 100

PL/SQL procedure successfully completed.


            X
-------------
          100

marcio@ORA10G> select * from emp where empno = :x;

        EMPNO ENAME      JOB                 MGR HIREDATE                      SAL          COMM        DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
          100 MARCIO     DBA                7698 15/08/2004 00:00:00          1950                          30

1 row selected.

marcio@ORA10G> update emp
  2     set sal = 10000
  3   where empno = :x;

1 row updated.

marcio@ORA10G>
marcio@ORA10G> connect /
Connected.
ops$marcio@ORA10G>
ops$marcio@ORA10G> column sql_text format a40 word_wrap
ops$marcio@ORA10G> column os_user       format a10
ops$marcio@ORA10G> column db_user       format a10
ops$marcio@ORA10G> column sql_bind format a25
ops$marcio@ORA10G>
ops$marcio@ORA10G> select timestamp, db_user, os_user, sql_text, sql_bind
  2    from dba_fga_audit_trail
  3   order by timestamp desc
  4  /

TIMESTAMP           DB_USER    OS_USER    SQL_TEXT                                 SQL_BIND
------------------- ---------- ---------- ---------------------------------------- -------------------------
31/05/2005 00:58:35 MARCIO     marcio     select * from emp where empno = :x and    #1(4):7782 #2(5):JAMES
                                          ename = :z

31/05/2005 00:58:35 MARCIO     marcio     insert into emp values ( 100,
                                          'MARCIO','DBA', 7698, '15/08/2004
                                          00:00:00', 1950, NULL, 30)

31/05/2005 00:58:35 MARCIO     marcio     select * from emp where empno = :x        #1(3):100
31/05/2005 00:58:35 MARCIO     marcio     update emp                                #1(3):100
                                          set sal = 10000
                                          where empno = :x

31/05/2005 00:58:34 MARCIO     marcio     select * from emp where empno = :x and    #1(4):7900 #2(5):JAMES
                                          ename = :z

31/05/2005 00:58:29 MARCIO     marcio     select * from emp

6 rows selected.

ops$marcio@ORA10G>

Labels: New Feature