课程第49/50次（2017-02-20星期日上下午）

botang · 发表于 2017-2-20 11:12:48

上完1Z0-052第08章（用户）
上完1Z0-052第11章（审计）
1Z0-052共19章（上完16章），1Z0-053共21章（上完15章），1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的31章

2017-02-19a.sql：

select * from dict where table_name like '%AUDIT%';
select * from dba_obj_audit_opts
where owner='HR';
select * from dba_priv_audit_opts;
select * from dba_stmt_audit_opts;
----
create tablespace tbsaudit datafile '/u01/app/oracle/oradata/orcl/tbsaudit01.dbf'
size 50M autoextend on ;
alter table aud$ move tablespace tbsaudit;
select a.OWNER,
a.OBJ_NAME,
a.OBJ_PRIVILEGE,
a.PRIV_USED,
a.SESSIONID,
a.SES_ACTIONS,
a.SQL_TEXT,
a.ACTION_NAME,
a.EXTENDED_TIMESTAMP
from dba_audit_trail a order by 9 desc;
audit update on hr.employees by session whenever successful ;
noaudit update on hr.employees whenever successful ;
audit update on hr.employees by access ;

复制代码

2017-02-19b.sql：

select * from dba_stmt_audit_opts;
audit table by hr by access;
grant drop any table to user3;
grant select any table to user3;
----
select * from dba_stmt_audit_opts;
select * from dba_priv_audit_opts;
audit drop any table by user3;
audit select any table by user3 by session;
---
create table tvalue ( a varchar2(1000)) 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 ( sys_context('userenv','os_user')||' at '||
to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') ||' modified new is '||:new.salary|| :old.salary
||' triggered is '||sys_context('userenv','current_user')||' triggerring '||sys_context('userenv','session_user')||
sys_context('userenv','authentication_type')||sys_context('userenv','ip_address') );
end if;
end;
select * from user_errors;
select * from dba_triggers t where t.TRIGGER_NAME='TRGVALUE';
select * from tvalue;

复制代码

2017-02-19c.sql：

select * from dict
where table_name like '%POLIC%';
select * from dba_audit_policies;
create table tfga ( a varchar2(1000)) tablespace tbsaudit;
CREATE OR REPLACE PROCEDURE procfga
( object_schema VARCHAR2, object_name VARCHAR2, policy_name
VARCHAR2 ) AS
begin
insert into tfga values ( sys_context('userenv','os_user')||' at '||
to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') ||
' the policy is '||policy_name||' '|| object_schema ||' '
|| object_name||' '||sys_context('userenv','current_user')||' '
||sys_context('userenv','session_user')||
sys_context('userenv','authentication_type')||sys_context('userenv','ip_address') );
end;
select * from tfga;
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.XML+dbms_fga.EXTENDED,
audit_column_opts => dbms_fga.ALL_COLUMNS);
end;
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.XML+dbms_fga.EXTENDED,
audit_column_opts => dbms_fga.ANY_COLUMNS);
end;

复制代码