SQL> conn hr/hr
SQL>@$ORACLE_HOME/sqlplus/demo/demobld.sql
— HR schema에서 emp,dept 테이블에 대한 통계정보 생성을 해야만, Auditing이 제대로 된다.
SQL>analyze table emp compute statistics;
SQL>analyze table dept compute statistics;
grant select on emp to sh;
grant select on dept to sh;
conn system/manager
begin
dbms_fga.add_policy(
OBJECT_SCHEMA=>’HR’,
OBJECT_NAME=>’EMP’,
policy_name=>’AUD_EMP_SAL_MGR’,
AUDIT_CONDITION=>’deptno in (10,20)’,
AUDIT_COLUMN=>’sal,mgr’,
handler_schema=>’system’,
handler_module=>’INS_EMP_TRAIL’,
enable=>TRUE);
end;
/
CREATE table AUD_EMP_TRAIL (
object_schema varchar2(80),
object_name varchar2(80),
policy_name varchar2(80)
);
CREATE PROCEDURE system.INS_EMP_TRAIL(
p_object_schema varchar2,
p_object_name varchar2,
p_policy_name varchar2)
AS
BEGIN
insert into system.AUD_EMP_TRAIL
values( p_object_schema,p_object_name,p_policy_name);
end;
/
1) sh로 접속
conn sh/sh
select * from hr.emp;
2) sh로 접속 HR의 EMP 테이블의 empno,ename,sal을 조건없이 조회
show user
select empno,ename,sal from hr.emp;
3) system으로 접속
conn system/manager
select ename from hr.emp where detpno=20;
select * from hr.emp where deptno in (10,20,30);
데이터 딕셔너리를 통해 Auditing 정보가 정상적으로 생성되었는 확인
show user
select to_char(timestamp,’YYYYDDHHH24MI’) as timestamp
db_user,policy_name,sql_text
from dba_fga_audit_trail;
select * from aud_emp_trail;