-- Audit policy to audit user account and privilege management
EXECUTE IMMEDIATE
'CREATE AUDIT POLICY ORA_ACCOUNT_MGMT ' ||
'ACTIONS CREATE USER, ALTER USER, DROP USER, ' ||
'CREATE ROLE, DROP ROLE, ALTER ROLE, ' ||
'SET ROLE, GRANT, REVOKE';
EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_ACCOUNT_MGMT IS '||
'''Audit policy containing audit options for auditing account ' || 'management actions ''';
-- Audit policy to audit Database parameter settings
EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_DATABASE_PARAMETER '||
'ACTIONS ALTER DATABASE, ALTER SYSTEM, CREATE SPFILE';
EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_DATABASE_PARAMETER IS '||
''' Audit policy containing audit options to audit changes '|| ' in database parameters''';
-- Audit Logon by failures
EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_LOGON_FAILURES ACTIONS LOGON';
EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_LOGON_FAILURES IS '||
'''Audit policy containing audit options to capture logon failures''';
-- Audit policy containing all Secure Configuration audit-options
-- Bug 20383779: audit BECOME USER by default in Unified Audit
EXECUTE IMMEDIATE
'CREATE AUDIT POLICY ORA_SECURECONFIG ' ||
'PRIVILEGES ALTER ANY TABLE, CREATE ANY TABLE, ' ||
'DROP ANY TABLE, CREATE ANY PROCEDURE, ' ||
'DROP ANY PROCEDURE, ALTER ANY PROCEDURE, '||
'GRANT ANY PRIVILEGE, ' ||
'GRANT ANY OBJECT PRIVILEGE, GRANT ANY ROLE, '||
'AUDIT SYSTEM, CREATE EXTERNAL JOB, ' ||
'CREATE ANY JOB, CREATE ANY LIBRARY, ' ||
'EXEMPT ACCESS POLICY, CREATE USER, ' ||
'DROP USER, ALTER DATABASE, ALTER SYSTEM, '||
'CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM, ' ||
'CREATE SQL TRANSLATION PROFILE, ' ||
'CREATE ANY SQL TRANSLATION PROFILE, ' ||
'DROP ANY SQL TRANSLATION PROFILE, ' ||
'ALTER ANY SQL TRANSLATION PROFILE, ' ||
'TRANSLATE ANY SQL, EXEMPT REDACTION POLICY, ' ||
'PURGE DBA_RECYCLEBIN, LOGMINING, ' ||
'ADMINISTER KEY MANAGEMENT, BECOME USER ' ||
'ACTIONS ALTER USER, CREATE ROLE, ALTER ROLE, DROP ROLE, '||
'SET ROLE, CREATE PROFILE, ALTER PROFILE, ' ||
'DROP PROFILE, CREATE DATABASE LINK, ' ||
'ALTER DATABASE LINK, DROP DATABASE LINK, '||
'CREATE DIRECTORY, DROP DIRECTORY, '||
'CREATE PLUGGABLE DATABASE, ' ||
'DROP PLUGGABLE DATABASE, '||
'ALTER PLUGGABLE DATABASE, '||
'EXECUTE ON DBMS_RLS, '||
'ALTER DATABASE DICTIONARY';
EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_SECURECONFIG IS '||
'''Audit policy containing audit options as per database '|| 'security best practices''';
-- Bug 17299076: audit policy with CIS recommended audit options
-- Bug 26040105: Update ORA_CIS_RECOMMENDATIONS policy per V2.0.0
-- (12-28-2016) FOR CIS BENCHMARK
EXECUTE IMMEDIATE 'CREATE AUDIT POLICY ORA_CIS_RECOMMENDATIONS '||
'PRIVILEGES SELECT ANY DICTIONARY, ALTER SYSTEM '||
'ACTIONS CREATE USER, ALTER USER, DROP USER, ' ||
'CREATE ROLE, DROP ROLE, ALTER ROLE, ' ||
'GRANT, REVOKE, CREATE DATABASE LINK, '||
'ALTER DATABASE LINK, DROP DATABASE LINK, '||
'CREATE PROFILE, ALTER PROFILE, DROP PROFILE, '||
'CREATE SYNONYM, DROP SYNONYM, '||
'CREATE PROCEDURE, DROP PROCEDURE, '||
'ALTER PROCEDURE, ALTER SYNONYM, CREATE FUNCTION, '||
'CREATE PACKAGE, CREATE PACKAGE BODY, '||
'ALTER FUNCTION, ALTER PACKAGE, ALTER SYSTEM, '||
'ALTER PACKAGE BODY, DROP FUNCTION, '||
'DROP PACKAGE, DROP PACKAGE BODY, '||
'CREATE TRIGGER, ALTER TRIGGER, '||
'DROP TRIGGER';
EXECUTE IMMEDIATE 'COMMENT ON AUDIT POLICY ORA_CIS_RECOMMENDATIONS IS '||
'''Audit policy containing audit options as per CIS recommendations''';
IF USER_CHOICE = RDBMS11_CHOICE THEN
-- 11g Secure Audit Configuration
-- Bug 20383779: audit BECOME USER by default in Traditional Audit
EXECUTE IMMEDIATE 'AUDIT ALTER ANY TABLE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE ANY TABLE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT DROP ANY TABLE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE ANY PROCEDURE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT DROP ANY PROCEDURE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ALTER ANY PROCEDURE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT GRANT ANY PRIVILEGE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT GRANT ANY ROLE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT AUDIT SYSTEM BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE EXTERNAL JOB BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE ANY JOB BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE ANY LIBRARY BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE PUBLIC DATABASE LINK BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT EXEMPT ACCESS POLICY BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ALTER USER BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE USER BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ROLE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE SESSION BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT DROP USER BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ALTER DATABASE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ALTER SYSTEM BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ALTER PROFILE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT DROP PROFILE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT DATABASE LINK BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT SYSTEM AUDIT BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT PROFILE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT PUBLIC SYNONYM BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT SYSTEM GRANT BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE SQL TRANSLATION PROFILE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT CREATE ANY SQL TRANSLATION PROFILE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT DROP ANY SQL TRANSLATION PROFILE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ALTER ANY SQL TRANSLATION PROFILE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT TRANSLATE ANY SQL BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT PURGE DBA_RECYCLEBIN BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT LOGMINING BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT EXEMPT REDACTION POLICY BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT ADMINISTER KEY MANAGEMENT BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT DIRECTORY BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT PLUGGABLE DATABASE BY ACCESS';
EXECUTE IMMEDIATE 'AUDIT BECOME USER BY ACCESS';
-- Audit configurarion on Common object in PDB is not supported.
-- Hence execute AUDIT on DBMS_RLS in non-CDB and CDB$ROOT.
IF (SYS_CONTEXT('USERENV', 'CON_ID') in (0,1)) THEN
EXECUTE IMMEDIATE 'AUDIT EXECUTE ON DBMS_RLS BY ACCESS';
END IF;
ELSIF USER_CHOICE = UNIAUD_CHOICE THEN
-- 12c Secure Audit Configuration
-- Enable ORA_SECURECONFIG for all users
EXECUTE IMMEDIATE 'AUDIT POLICY ORA_SECURECONFIG';
-- Also enable Logon failures. Bug 18174384
EXECUTE IMMEDIATE 'AUDIT POLICY ORA_LOGON_FAILURES WHENEVER NOT SUCCESSFUL';
ELSE
DBMS_OUTPUT.PUT_LINE('Invalid Input "' || USER_CHOICE || '". Please try again');
END IF;
The above is not the full code from the file but the parts most relevant to understanding the difference between the choice of 11g versus 12c. |