Bo's Oracle Station

查看: 2361|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 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

  1. select  * from dict where table_name like '%AUDIT%';

  2. select  * from dba_obj_audit_opts  
  3. where owner='HR';

  4. select  * from dba_priv_audit_opts;

  5. select  * from dba_stmt_audit_opts;

  6. ----
  7. create tablespace tbsaudit datafile '/u01/app/oracle/oradata/orcl/tbsaudit01.dbf'
  8. size 50M autoextend on   ;

  9. alter table aud$ move tablespace tbsaudit;

  10. select  a.OWNER,
  11.            a.OBJ_NAME,
  12.                    a.OBJ_PRIVILEGE,
  13.                    a.PRIV_USED,
  14.                    a.SESSIONID,
  15.                    a.SES_ACTIONS,
  16.                    a.SQL_TEXT,
  17.                    a.ACTION_NAME,
  18.                    a.EXTENDED_TIMESTAMP
  19. from dba_audit_trail  a order by 9 desc;

  20. audit update on hr.employees by session whenever  successful ;

  21. noaudit update on hr.employees  whenever  successful ;

  22. audit update on hr.employees by  access ;
复制代码

2017-02-19b.sql:
  1. select  * from dba_stmt_audit_opts;

  2. audit table by hr by access;

  3. grant drop any table to user3;

  4. grant select any table to user3;


  5. ----
  6. select  * from dba_stmt_audit_opts;

  7. select  * from dba_priv_audit_opts;

  8. audit drop any table by user3;

  9. audit select  any table by user3 by session;

  10. ---

  11. create table tvalue ( a varchar2(1000)) tablespace tbsaudit;




  12. create or replace trigger trgvalue
  13. after update of salary on hr.employees
  14. referencing new as new old as old
  15. for each row
  16. begin
  17.   if :old.salary != :new.salary then
  18.      insert into tvalue   values (  sys_context('userenv','os_user')||' at '||
  19.                         to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') ||' modified new is '||:new.salary|| :old.salary
  20.                                         ||' triggered is   '||sys_context('userenv','current_user')||' triggerring   '||sys_context('userenv','session_user')||
  21.                     sys_context('userenv','authentication_type')||sys_context('userenv','ip_address') );      
  22.   end if;
  23. end;

  24. select  * from user_errors;

  25. select  * from dba_triggers t where t.TRIGGER_NAME='TRGVALUE';

  26. select  * from  tvalue;

  27.    




复制代码

2017-02-19c.sql:
  1. select  * from dict
  2. where table_name like '%POLIC%';

  3. select  * from dba_audit_policies;

  4. create table tfga ( a varchar2(1000)) tablespace tbsaudit;


  5. CREATE OR REPLACE PROCEDURE procfga
  6.   ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
  7.   VARCHAR2 ) AS
  8. begin
  9.    insert into tfga   values ( sys_context('userenv','os_user')||' at '||
  10.                         to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') ||
  11.                                         ' the  policy is '||policy_name||' '|| object_schema ||' '
  12.                                         ||  object_name||' '||sys_context('userenv','current_user')||'  '
  13.                                         ||sys_context('userenv','session_user')||
  14.                     sys_context('userenv','authentication_type')||sys_context('userenv','ip_address') );
  15.                                        
  16. end;

  17. select  * from tfga;

  18.   
  19.   begin
  20.      dbms_fga.add_policy(object_schema => 'HR',
  21.      object_name => 'EMPLOYEES',
  22.      policy_name => 'POLICY1',
  23.      audit_condition => 'department_id=20',
  24.      audit_column => 'salary,commission_pct',
  25.      handler_schema => 'SYS',
  26.      handler_module => 'PROCFGA',
  27.      enable => true,
  28.      statement_types => 'SELECT,UPDATE',
  29.      audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
  30.      audit_column_opts => dbms_fga.ALL_COLUMNS);
  31.    end;


  32.     begin
  33.     dbms_fga.drop_policy('HR','EMPLOYEES','POLICY1');
  34.         end;

  35.   
  36.   begin
  37.      dbms_fga.add_policy(object_schema => 'HR',
  38.      object_name => 'EMPLOYEES',
  39.      policy_name => 'POLICY1',
  40.      audit_condition => 'department_id=20',
  41.      audit_column => 'salary,commission_pct',
  42.      handler_schema => 'SYS',
  43.      handler_module => 'PROCFGA',
  44.      enable => true,
  45.      statement_types => 'SELECT,UPDATE',
  46.      audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
  47.      audit_column_opts => dbms_fga.ANY_COLUMNS);
  48.    end;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 22:06 , Processed in 0.047022 second(s), 24 queries .

快速回复 返回顶部 返回列表