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