课程第48次：2016-07-15星期五

botang

课程第48次：2016-07-15星期五
审计中的1.强制审计
             2.标准审计
             3.细粒度审计


2016-07-15_standard_mandatory.sql：

1. select  * from dba_audit_trail  order by 5 desc;
   
2. 
   
3. 
   
4. 
   
5. select  * from DBA_AUDIT_SESSION  order by 5 desc;
   
6. 
   
7. 
   
8. select  * from  dba_obj_audit_opts;
   
9. 
   
10. select  * from dba_priv_audit_opts  order by 3;
    
11. 
    
12. select  * from dba_stmt_audit_opts
    
13. minus
    
14. select  * from dba_priv_audit_opts  order by 3;
    
15. 
    
16. 
    
17. ---
    
18. alter user sysman identified by oracle_4U;
    
19. alter user dbsnmp identified by oracle_4U;
    
20. ----
    
21. 
    
22. 
    
23. noaudit drop any table ;
    
24. 
    
25. audit drop any table by hr by access whenever successful;
    
26. 
    
27. select  * from dba_priv_audit_opts  order by 3;
    
28. 
    
29. ---
    
30. select  * from dba_sys_privs p where p.grantee='HR';
    
31. 
    
32. grant drop any table to hr;
    
33. ---
    
34. 
    
35. alter user sh identified by sh account  unlock;
    
36. 
    
37. ----
    
38. 
    
39. select  * from dba_views v where v.view_name='DBA_AUDIT_TRAIL';
    
40. 
    
41. select  count(*) from dba_audit_trail
    
42. union all
    
43. select  count(*)  from aud$;
    
44. 
    
45. ----
    
46. create tablespace tbsaudit datafile size 20M
    
47. autoextend on  ;
    
48. 
    
49. 
    
50. alter table aud$ move tablespace tbsaudit ;
    
51. 
    
52. -----
    
53. select  * from v$xml_audit_trail  order by 6 desc ;
    
54. 
    
55. ---
    
56. 
    
57. 
    
58. select  * from dba_common_audit_trail  order by 6 desc ;
    
59. ---
    
60. select  * from dba_sys_privs p where p.grantee='HR';
    
61. 
    
62. ---
    
63. grant select any table to hr;
    
64. 
    
65. audit select any table by hr by session whenever  successful;
    
66. 
    
67. select  * from dba_priv_audit_opts  order by 3;
    
68. 
    
69. ----
    
70. 
    
71. select  * from dba_common_audit_trail  order by 6 desc ;
    
72. 
    
73. select  * from v$xml_audit_trail  order by 6 desc ;
    
74. ----
    
75. select  * from  dba_obj_audit_opts;
    
76. 
    
77. select  * from v$xml_audit_trail  order by 6 desc ;
    
78. 
    
79. 
    
80. ---
    
81. select  * from dba_stmt_audit_opts
    
82. minus
    
83. select  * from dba_priv_audit_opts  order by 3;
    
84. 
    
85. 
    
86. audit table by hr by access whenever successful;
    
87. 
    

复制代码

2016-07-15_fga.sql：

1. select  * from dict
   
2. where table_name like 'DBA%POLICIES%';
   
3. 
   
4. select  * from
   
5. DBA_AUDIT_POLICY_COLUMNS;
   
6. 
   
7. select  * from
   
8. DBA_AUDIT_POLICIES;
   
9. 
   
10. ---
    
11. select  
    
12.      user,
    
13.      sys_context('userenv','current_user')  ,
    
14.      sys_context('userenv','session_user') ,
    
15.      sys_context('userenv','ip_address')  ,
    
16.                 sysdate,
    
17.      sys_context('userenv','ip_address')  ,
    
18.      sys_context('userenv','authentication_type')
    
19.      from dual;
    
20.      
    
21.      create table tfga ( a varchar2(2000))   
    
22.       tablespace tbsaudit;
    
23. 
    
24. 
    
25. 
    
26. 
    
27. 
    
28. 
    
29. 
    
30. 
    
31. CREATE OR REPLACE PROCEDURE procfga
    
32.    ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )
    
33.    AS
    
34. 
    
35. begin
    
36.    insert into   tfga  values(   user||' '||
    
37.      sys_context('userenv','current_user')|| ' '||
    
38.      sys_context('userenv','session_user')||' '||
    
39.      sys_context('userenv','ip_address') ||' '||
    
40.                to_char( sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
    
41.      sys_context('userenv','ip_address')||' '||
    
42.      sys_context('userenv','authentication_type') ||' '||object_schema||' '||
    
43.      object_name||' '||policy_name
    
44.    
    
45.        );
    
46. end;
    
47. 
    
48. select  * from user_errors;
    

复制代码