-
Notifications
You must be signed in to change notification settings - Fork 1
/
daua_pol.sql
128 lines (116 loc) · 5.33 KB
/
daua_pol.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
--------------------------------------------------------------------------------
-- OraDBA - Oracle Database Infrastructur and Security, 5630 Muri, Switzerland
--------------------------------------------------------------------------------
-- Name......: daua_pol.sql
-- Author....: Stefan Oehrli (oes) [email protected]
-- Editor....: Stefan Oehrli
-- Date......: 2023.07.06
-- Usage.....:
-- Purpose...: Disable all audit policies and drop all non-Oracle maintained policies
-- Notes.....:
-- Reference.:
-- License...: Apache License Version 2.0, January 2004 as shown
-- at http://www.apache.org/licenses/
--------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET LINESIZE 160 PAGESIZE 200
COL policy_name FOR A40
COL entity_name FOR A30
COL comments FOR A80
SPOOL daua_audpol.log
SHOW con_name
-- List enabled audit policies
SELECT * FROM audit_unified_enabled_policies;
-- Disable all policies which are not from AVDF identified by policy name 'ORA_AV$'
DECLARE
v_sql VARCHAR2(4000);
BEGIN
FOR r_audit_unified_enabled_policies IN (
SELECT policy_name,entity_name,entity_type
FROM audit_unified_enabled_policies
WHERE policy_name NOT LIKE 'ORA_AV$%' OR entity_name IN ('SYSDG','SYSBACKUP')) LOOP
IF r_audit_unified_enabled_policies.entity_name='ALL USERS' THEN
v_sql := 'NOAUDIT POLICY '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.policy_name);
ELSIF r_audit_unified_enabled_policies.entity_type='ROLE' THEN
v_sql := 'NOAUDIT POLICY '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.policy_name)
|| ' BY USERS WITH GRANTED ROLES '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.entity_name);
ELSE
v_sql := 'NOAUDIT POLICY '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.policy_name)
|| ' BY '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.entity_name);
END IF;
-- display NOAUDIT statement
dbms_output.put_line('INFO : execute '||v_sql);
--- execute NOAUDIT statement
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
-- workaround for BUG 30769454 Policy Created For Some Actions Are Not Showing In Audit_Unified_Policies
DECLARE
v_sql VARCHAR2(4000);
BEGIN
FOR r_audit_unified_enabled_policies IN (
SELECT object_name, object_type FROM dba_objects
WHERE object_type = 'UNIFIED AUDIT POLICY' AND
object_name NOT IN (SELECT policy_name FROM audit_unified_enabled_policies WHERE policy_name NOT LIKE 'ORA_AV$%')
ORDER BY object_name) LOOP
v_sql := 'NOAUDIT POLICY '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.object_name);
-- display NOAUDIT statement
dbms_output.put_line('INFO : execute '||v_sql);
--- execute NOAUDIT statement
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
-- List enabled audit policies
SELECT * FROM audit_unified_enabled_policies;
-- List audit policies not maintained by Oracle
SELECT policy_name, common, oracle_supplied FROM audit_unified_policies
WHERE oracle_supplied<>'YES' GROUP BY policy_name, common, oracle_supplied;
-- Drop all audit policies which are not provided by Oracle. Either where
-- oracle_supplied<>'YES' AND policy_name NOT LIKE 'ORA_AV$'
DECLARE
v_sql VARCHAR2(4000);
BEGIN
FOR r_audit_unified_enabled_policies IN (SELECT policy_name, common FROM audit_unified_policies WHERE oracle_supplied<>'YES' AND policy_name NOT LIKE 'ORA_AV$%' GROUP BY policy_name, common ) LOOP
v_sql := 'DROP AUDIT POLICY '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.policy_name);
-- display DROP AUDIT statement
dbms_output.put_line('INFO : execute '||v_sql);
--- execute DROP AUDIT statement
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
-- workaround for BUG 30769454 Policy Created For Some Actions Are Not Showing In Audit_Unified_Policies
DECLARE
v_sql VARCHAR2(4000);
BEGIN
FOR r_audit_unified_enabled_policies IN (
SELECT object_name, object_type FROM dba_objects
WHERE object_type = 'UNIFIED AUDIT POLICY' AND
object_name NOT IN (SELECT policy_name FROM audit_unified_policies WHERE oracle_supplied<>'YES' AND policy_name NOT LIKE 'ORA_AV$%' )
ORDER BY object_name) LOOP
v_sql := 'NOAUDIT POLICY '
|| sys.dbms_assert.enquote_name(r_audit_unified_enabled_policies.object_name);
-- display NOAUDIT statement
dbms_output.put_line('INFO : execute '||v_sql);
--- execute NOAUDIT statement
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
-- List audit policies not maintained by Oracle
SELECT policy_name, common, oracle_supplied FROM audit_unified_policies
WHERE oracle_supplied<>'YES' GROUP BY policy_name, common, oracle_supplied;
-- List Oracle maintained audit policies
SELECT policy_name, common, oracle_supplied FROM audit_unified_policies
WHERE oracle_supplied='YES' GROUP BY policy_name, common, oracle_supplied;
SPOOL off
-- EOF -------------------------------------------------------------------------