第50次：2015-09-01星期一

botang · 发表于 2015-9-1 08:52:22

本帖最后由 botang 于 2015-9-3 22:08 编辑

上完1Z0-052第11章（52-43）

<font
color="#ff0000">上完1Z0-052第11章
（52-43）
select spid
from v$session s , v$process p
where s.paddr=p.addr and s.program like '%plsql%';
select * from aud$;
select * from dba_tables t where t.table_name='AUD
;
create tablespace tbsaudit datafile size 20M autoextend on;
alter table aud$ move tablespace tbsaudit ;
select * from dba_obj_audit_opts;
select * from dba_stmt_audit_opts
intersect
select * from dba_priv_audit_opts order by 3;
select * from dba_audit_trail where username <> 'DBSNMP' and username <> 'SYSMAN'
order by timestamp desc ;
truncate table aud$;
select * from v$xml_audit_trail where db_user <> 'DBSNMP' and db_user <> 'SYSMAN'
order by extended_timestamp desc ;
select * from dba_common_audit_trail where db_user='HR' ;
select * from dba_audit_trail where username <> 'DBSNMP' and username <> 'SYSMAN'
order by timestamp desc ;
select * from dba_stmt_audit_opts
minus
select * from dba_priv_audit_opts order by 3;
select * from dba_obj_audit_opts ;
select sys_context('userenv','ip_address'),
sys_context('userenv','session_user') ,
sys_context('userenv','authentication_type')
from dual;
drop table tfga;
create table tfga(
c_policyname varchar2(30),
c_username varchar2(30),
c_tablename varchar2(30),
c_date date ,
c_adress varchar2(40),
c_sessionuser varchar2(30),
c_authentication varchar2(30)
) tablespace tbsaudit;
create or replace procedure procfga
(p_owner varchar2,
p_tablename varchar2,
p_policyname varchar2)
is
begin
insert into tfga values (
p_policyname,
p_owner,
p_tablename,
sysdate,
sys_context('userenv','ip_address'),
sys_context('userenv','session_user'),
sys_context('userenv','authentication_type'));
end;
select * from user_errors;
select * from dba_audit_policies;
select * from dba_policies;
begin
dbms_fga.drop_policy('HR','EMPLOYEES',
'POLICY1');
end;
begin
dbms_fga.add_policy(object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'POLICY1',
audit_condition => 'department_id=20 ',
audit_column => 'salary,commission_pct',
handler_schema => 'SYS',
handler_module => 'PROCFGA',
enable => true,
statement_types => 'SELECT,UPDATE',
audit_trail => dbms_fga.DB_EXTENDED,
audit_column_opts => dbms_fga.ANY_COLUMNS);
end;
select * from dba_fga_audit_trail;
select * from tfga;
select * from dba_common_audit_trail;
create table tvalue(
c_username varchar2(30),
c_tablename varchar2(30),
c_date date ,
c_adress varchar2(40),
c_sessionuser varchar2(30),
c_authentication varchar2(30) ,
old_salary number(8,2),
new_salary number(8,2)
) tablespace tbsaudit;
create or replace trigger trgvalue
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 tvalue
values ('HR',
'EMPLOYEES',
sysdate,
sys_context('userenv','ip_address'),
sys_context('userenv','current_user'),
sys_context('userenv','authentication_type'),
:old.salary,
:new.salary
);
end if;
end;
select * from user_errors;
select * from tvalue;

复制代码