Oracle Secure Configuration
Versions: 12.1 - 21c

Overview
Beginning with Oracle Database version 12.1 Oracle added a new file named secconf.sql to the $ORACLE_HOME/rdbms/admin directory that is run by default when a new database is created by not when a database is upgraded using DBUA (Database Upgrade Assistant).

Due to the above it is easily possible to have two different Oracle Databases, both version 12.1, 12.2, 18.3, 19.3, or above and have differences in the security configuration. You must read the full secconf.sql file for the version you are using as a roadmap to understand the differences that may exist in different databases with the exact same version number.

From our standpoint, we find the new Container architecture substantially more secure than the previous legacy architecture and the new Unified Audit Policies created by this script better than legacy auditing.

Use the following to information to help you understand the impact of leveraging secure configuration.
 
Source Code
File Header
Rem
Rem $Header: rdbms/admin/secconf.sql /main/25 2020/07/20 02:13:04 dgoddard Exp $
Rem
Rem secconf.sql
Rem
Rem Copyright (c) 2006, 2020, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem secconf.sql - SECure CONFiguration script
Rem

Rem DESCRIPTION
Rem Secure configuration settings for the database include a reasonable
Rem default password profile, password complexity checks, audit settings
Rem (enabled, with admin actions audited), and as many revokes from PUBLIC
Rem as possible. In the first phase, only the default password profile is
Rem included.
Rem
Rem
Rem NOTES
Rem Only invoked for newly created databases, not for upgraded databases

Note that even though the file header references revokes from PUBLIC ... none take place.
???
 
Default Profile
Rem Secure configuration settings. Currently, only the default password
Rem profile is included, without the password complexity check and has
Rem the recommended audit settings. We will add the revokes from PUBLIC, and
Rem the password complexity checks.

-- Create password profile without a password complexity routine, for backward
-- compatibility. Add the routine if possible without breaking tests
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
INACTIVE_ACCOUNT_TIME UNLIMITED;

Note that this DEFAULT profile is still woefully inadequate. Use the PROFILE link at page bottom. Follow the link to the section on the PROFILES page named "Maximum Security Default Profile."

A "Secure Profile" is one that guarantees that any user/schema created without the knowledge of the Oracle DBA and Security team will be unable to log on and unable to perform any malicious activity of any type whatsoever.
 
Auditing Version
PROMPT Do you wish to configure 11g style Audit Configuration OR
PROMPT Do you wish to configure 12c Unified Audit Policies?
PROMPT Enter RDBMS_11G for former or RDBMS_UNIAUD for latter

When the script is running there will be 3 prompts for input. 11g style Audit Configuration means legacy auditing. 12c Unified Audit Policies is an indication that the new 12c technology should be deployed.
 
Unified Audit Policies Included In Version 21c
-- 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.
 
Data Dictionary Update
update sys.aud_policy$ set type= type + 512
where (bitand(type, 512) = 0) and policy# in
(select distinct obj# from sys.obj$ where namespace=93 and type#=115 and
name in (
'ORA_CIS_RECOMMENDATIONS',
'ORA_DATABASE_PARAMETER',
'ORA_ACCOUNT_MGMT',
'ORA_LOGON_FAILURES',
'ORA_SECURECONFIG',
'COMMON_USER_LOGONS',
'SYS_USER_ACTIONS'));
COMMIT;

The audit policies data dictionary table is updated to reflect deployed Unified Audit Policies available for use. You can, of course create your own audit policies customized to specific requirements unique to your organization and its databases.
 
Conclusion
We recommend, in the strongest terms, moving forward from Oracle's legacy architecture to the new Container Database (CDB & PDB) architecture. As part of that move be sure that you improve the manageability and maintainability of your databases by replacing legacy auditing with the new Unified Audit Policies and upgrade using a technique that includes executing the secconf.sql script.

Related Topics
EXECSEC.SQL