/*+ oracle no:comments */ Auditing et Fine Grained Auditing

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