第40次：2015-04-23星期四

botang · 发表于 2015-4-24 15:02:00

select * from dba_obj_audit_opts;
select * from dba_stmt_audit_opts;
select * from dba_priv_audit_opts order by 3 ;
select * from dba_common_audit_trail where db_user='SH' order by 6 desc;
select * from dba_audit_trail;
noaudit drop any table ;
audit drop any table by sh whenever successful;
grant drop any table to sh;
select table_name, tablespace_name from dba_tables where tablespace_name ='TBSAUDIT';
alter table taudit1 move tablespace tbsaudit;
select * from taudit1;
create table taudit2 tablespace tbsaudit as select * from taudit1 where 1=2;
alter table taudit2 add ( old_value number(8,2) , new_value number(8,2)) ;
alter table taudit2 add ( employee_id number(4)) ;
select * from taudit2;
CREATE OR REPLACE TRIGGER hrsalary_audit
AFTER UPDATE OF salary ON hr.employees
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :old.salary != :new.salary
THEN
INSERT INTO taudit2
VALUES (sys_context('userenv','session_user'), sysdate, sys_context('userenv','ip_address'),
sys_context('userenv','authentication_method'),
:old.salary , :new.salary , :new.employee_id );
END IF;
END;
select * from user_errors;
select * from dba_triggers t where t.trigger_name='HRSALARY_AUDIT';
select * from dba_fga_audit_trail;
select * from dict where table_name like 'DBA%POLIC%';
select * from DBA_AUDIT_POLICIES;
---
select text from dba_source where name='DBMS_FGA' and type='PACKAGE';
---
begin
dbms_fga.add_policy(
OBJECT_SCHEMA =>'HR',
OBJECT_NAME =>'EMPLOYEES',
POLICY_NAME=>'POLICY1',
AUDIT_CONDITION =>'department_id=20',
AUDIT_COLUMN =>'salary,commission_pct',
STATEMENT_TYPES =>'SELECT,INSERT,UPDATE,DELETE',
AUDIT_TRAIL =>5,
handler_schema => 'SYS',
handler_module => 'PROC_FGA' );
end;
select * from dba_common_audit_trail where db_user='HR' order by 6 desc;
create table taudit3 tablespace tbsaudit as select * from taudit1 where 1=2;
alter table taudit3 add ( p_owner varchar2(30),
p_object varchar2(30),
p_policy varchar2(30));
create or replace procedure proc_fga(p_owner varchar2 ,
p_object varchar2,
p_policy varchar2)
is
begin
insert into taudit3 values ( sys_context('userenv','session_user'), sysdate, sys_context('userenv','ip_address'),
sys_context('userenv','authentication_method'), p_owner, p_object, p_policy);
end;
select * from user_errors;
select * from dba_objects o where o.owner='SYS' and o.object_name='PROC_FGA';
---
begin
dbms_fga.drop_policy( OBJECT_SCHEMA=>'HR', object_name => 'EMPLOYEES', policy_name => 'POLICY1');
end;
---
select * from taudit3;

复制代码