Fine Grained Auditing – Select de la colonne « SAL »

connect sys/change_on_install@orcl as sysdba

grant execute on dbms_fga to scott;
grant select on dba_fga_audit_trail;

create user apps
identified by apps
default tablespace users
temporary tablespace temp;

grant connect to apps;

create user gregory
identified by gregory
default tablespace users
temporary tablespace temp;

grant create session to gregory;
create role gregrole;
grant select on scott.emp to gregrole;
grant gregrole to gregory
alter user gregory grant connect through apps with roles gregrole;

connect scott/tiger@orcl

begin
dbms_fga.add_policy( object_schema => 'SCOTT',
            object_name     => 'EMP',
            policy_name     => 'SAL',
            audit_condition => 'sal is not null',
            audit_column    => 'SAL' );
end;
/

col object_name format a6
col policy_name format a6
col policy_text format a50
col enabled format a3
col sel format a3
select object_name, policy_name, policy_text, enabled, sel
from user_audit_policies;

OBJECT POLICY POLICY_TEXT                                        ENA SEL
------ ------ -------------------------------------------------- --- ---
EMP    SAL    sal is not null                                    YES YES

connect apps[gregory]/apps@orcl

alter session set current_schema=scott;

select empno, ename from emp where ename='KING';

select * from emp where ename = 'KING';

connect scott/tiger@orcl

select db_user, sql_text from dba_fga_audit_trail;

DB_USER       SQL_TEXT
------------- ---------------------------------------------
GREGORY       select * from emp where ename = 'KING'

begin
dbms_fga.drop_policy( object_schema => 'SCOTT',
            object_name     => 'EMP',
            policy_name     => 'SAL');
end;
/

select * from user_audit_policies;

Auditing – CREATE SESSION

connect / as sysdba

alter system set audit_trail=DB,EXTENDED scope=spfile;
startup force;

connect sys/change_on_install@orcl as sysdba

delete from sys.aud$;
audit session by gregory;

col USER_NAME format a12
col AUDIT_OPTION format a20
select USER_NAME, AUDIT_OPTION, SUCCESS, FAILURE
from DBA_STMT_AUDIT_OPTS;

USER_NAME    AUDIT_OPTION         SUCCESS    FAILURE
------------ -------------------- ---------- ----------
APPS         CREATE SESSION       BY ACCESS  BY ACCESS

connect apps[gregory]/apps@orcl

connect apps[gregory]/apps@orcl

connect apps[gregory]/apps@orcl

connect apps[gregory]/apps@orcl

connect sys/change_on_install@orcl as sysdba

set lines 120
col username format a10
col comment_text format a80
col priv_used format a15
select username, priv_used, comment_text from  dba_audit_trail;
USERNAME   PRIV_USED       COMMENT_TEXT
---------- --------------- -------------------------------------------------------------------------
GREGORY    CREATE SESSION  Authenticated by: PROXY; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=200
                         (PORT=1673))

GREGORY    CREATE SESSION  Authenticated by: PROXY; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=200
                         (PORT=1674))

GREGORY    CREATE SESSION  Authenticated by: PROXY; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=200
                         (PORT=1675))

GREGORY    CREATE SESSION  Authenticated by: PROXY; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=200
                         (PORT=1672))