Oracle Lockdown Profiles
Versions 12.2 - 19.3

Security Advisory
One of the most important concepts in IT security is "Minimize the Attack Surface." In the Oracle Database we often say it as "Least Privileges" or "Grant Minimum Privileges." The point, no matter how you say it, is to give users the tools they need to do their job but don't give them unnecessary tools that increase risk to system integrity.

Here's the classic conundrum. We want to grant developers the ability to do ALTER SYSTEM SET PLSQL_WARNING but it would be irresponsible in doing so to also allow every developer to perform an ALTER SYSTEM SET and change initialization parameters. Developers do not need to, and should not be able to, alter memory allocation: It just isn't in their area of expertise or job description. A lockdown profile is a new type of database object that increases security through disabling features, options, and statements or, creates increased granularity so that portions of statements or system privileges can be selectively enabled or disabled.

But Lockdown Profiles can do more than just enhance security with system privs. As you will see below in the "How Oracle Works" a Lockdown Profile can enhance security by limiting access to built-in capabilities such as network and operating system access, from specific built-in packages such as UTL_INADDR but equally important though it is not strictly the raison d'etre of this website, enforce license restrictions such as PARTITIONING.

Before reading this monograph be sure you are familiar with System Privileges by reviewing the monograph linked at the bottom of this page.
 
Rant
We will state this as simply as we can and in a way that Oracle Corp. cannot.

The Oracle multi-tenant container architecture is superior in essentially every respect to the legacy architecture. The only reason the entire community has not changed architectures is that the the overwhelming majority of Oracle DBAs have become to Oracle, what IBM's admins became to IBM after a similar number of years. Oracle can build a better mouse trap but the customers will remain for the most part ignorant of the new capabilities and, if they do become aware of them, can dream up endless excuses to refuse to use them even when free.

Have Oracle DBAs embraced Edition Based Redefinition? No.
Have Oracle DBAs embraced Container databases with PDBs? No.
All are free and all are critical product improvements.

Sadly, we expect that Lockdown Profiles will be added to this list. Lockdown Profiles are a critical tool to securing an Oracle Database environment. Learn what they are, learn the syntax, and use them liberally to tighten security.
 
Recommended Security Rules

 NEVER
  • Deploy a legacy architecture Oracle Database if you can deploy a Container Database
  • Grant system privileges to any database user or application without looking for the opportunity to tighten security with a Lockdown Profile
 WITH GREAT CARE
  • Define the Lockdown Profile to truly minimize the scope of the privilege being granted to minimize risk
 CAUTIONS
  • When creating separate rules in multiple PDBs, write the rules before writing the code make verify that they are internally consistent and do not create gaps
 
How Oracle Works
This working demo addresses a security issue that goes back in history at least 30 years. How can I grant a developer, in a test environment ALTER SYSTEM to perform their work without also granting them ALTER SYSTEM to change initialization parameters. SQL> conn sys@pdbdev as sysdba
Connected.

SQL> CREATE LOCKDOWN PROFILE dev_pdbs;

Lockdown Profile created.

SQL> SELECT owner, object_type
  2  FROM dba_objects
  3  WHERE object_name = 'DEV_PDBS';

OWNER                     OBJECT_TYPE
------------------------- -----------------
PUBLIC                    LOCKDOWN PROFILE

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE STATEMENT=('ALTER SYSTEM')
  3  CLAUSE=('SET')
  4* OPTION ALL EXCEPT = ('PLSQL_DEBUG', 'PLSQL_WARNINGS');

Lockdown Profile altered.

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE OPTION=('PARTITIONING');

Lockdown Profile altered.

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE FEATURE=('NETWORK_ACCESS', 'UTL_TCP');

Lockdown Profile altered.

SQL> col profile_name format a20
SQL> col rule_type format a20
SQL> col rule format a20
SQL> col clause format a20

SQL> SELECT profile_name, rule_type, rule, clause, status
  2  FROM cdb_lockdown_profiles;

PROFILE_NAME   RULE_TYPE  RULE             CLAUSE  STATUS   CLAUSE_OPTION
-------------- ---------- ---------------- ------- -------- --------------
DEV_PDBS       FEATURE    NETWORK_ACCESS           DISABLE
DEV_PDBS       FEATURE    UTL_TCP                  DISABLE
DEV_PDBS       OPTION     PARTITIONING             DISABLE
DEV_PDBS       STATEMENT  ALTER SYSTEM     SET     DISABLE
DEV_PDBS       STATEMENT  ALTER SYSTEM     SET     ENABLE   PLSQL_DEBUG
DEV_PDBS       STATEMENT  ALTER SYSTEM     SET     ENABLE   PLSQL_WARNINGS
PRIVATE_DBAAS  EMPTY
PUBLIC_DBAAS   EMPTY
SAAS           EMPTY

SQL> show parameter lockdown

NAME          TYPE    VALUE
------------- ------- -----------
pdb_lockdown  string

SQL> ALTER SYSTEM SET pdb_lockdown = 'DEV_PDBS' SID='*' scope=BOTH;

System altered.

SQL> show parameter lockdown

NAME          TYPE    VALUE
------------- ------- -----------
pdb_lockdown  string  DEV_PDBS

SQL> ALTER SESSION SET CONTAINER=PDBDEV;

Session altered.

SQL> ALTER SYSTEM SET pdb_lockdown = 'DEV_PDBS' SID='*' scope=BOTH;

System altered.

SQL> GRANT alter system TO pdb_user;

SQL ALTER SESSION SET CONTAINER = CDB$ROOT;

Session altered.

SQL> ALTER SYSTEM RESET pdb_lockdown SID='*' scope=BOTH;

System altered.
 
LOCKDOWN PROFILE Information
Dependencies
CDB_LOCKDOWN_PROFILES DBA_LOCKDOWN_PROFILES LOCKDOWN_PROF$
Documented Yes
Exceptions
Error Code Reason
ORA-65207 Invalid statement or feature or option specified
First Available 12.2
Managed Features
Feature Name Operation
AWR_ACCESS
AWR_ACCESS The PDB taking manual and automatic Automatic Workload Repository (AWR) snapshots
COMMON_SCHEMA_ACCESS
COMMON_USER_LOCAL_SCHEMA_ACCESS A common user invoking an invoker’s rights code unit or accessing a BEQUEATH CURRENT_USER view owned by any local user in the PDB
LOCAL_USER_COMMON_SCHEMA_ACCESS Local user with an ANY system privilege (eg. CREATE ANY TABLE) creating or accessing objects in a common user’s schema for which the privilege applies.
SECURITY_POLICIES Creation of security policies by a local user on a common object, including:
• Data Redaction
• Fine Grained Auditing (FGA)
• Real Application Security (RAS)
• Virtual Private Database (VPD)
CONNECTIONS
LOCAL_SYSOPER_RESTRICTED_MODE_CONNECT Local user with SYSOPER privs connecting to a PDB that is open in RESTRICTED mode
CTX_LOGGING
CTX_LOGGING Oracle Text PL/SQL procedures logging
JAVA
JAVA Disables all options and features of the database that depend on Java
JAVA_RUNTIME
JAVA Disables all options and features of the database that depend on Java
NETWORK_ACCESS
AQ_PROTOCOLS Use of HTTP, SMTP, and OCI notification features (DBMS_AQADM, DBMS_AQADM_SYS)
CTX_PROTOCOLS Operations that access the Oracle Text datastore types, FILE_DATASTORE and URL_DATASTORE

Printing tokens as part of CTX logging with events EVENT_INDEX_PRINT_TOKEN and EVENT_OPT_PRINT_TOKEN
DBMS_DEBUG_JDWP DBMS_DEBUG_JDWP package
UTL_HTTP UTL_HTTP package
UTL_INADDR UTL_INADDR package
UTL_SMTP UTL_SMTP package
UTL_TCP UTL_TCP package
XDB_PROTOCOLS Using HTTP, FTP, and other network protocols through XDB
OS_ACCESS
DROP_TABLESPACE_KEEP_DATAFILES Dropping a PDB tablespace without specifying the INCLUDING CONTENTS AND DATAFILES clause
EXTERNAL_FILE_ACCESS Using external files or directory objects in the PDB when PATH_PREFIX is not set for the PDB
EXTERNAL_PROCEDURES External procedure agent EXT_PROC
FILE_TRANSFER DBMS_FILE_TRANSFER package
JAVA_OS_ACCESS Use of java.io.FilePermissionfrom Java
LOB_FILE_ACCESS Use of BFILE and CFILE data types
TRACE_VIEW_ACCESS Accessing the following trace views:
• [G]V$DIAG_OPT_TRACE_RECORDS
• [G]V$DIAG_SQL_TRACE_RECORDS
• [G]V$DIAG_TRACE_FILE_CONTENTS
• V$DIAG_SESS_OPT_TRACE_RECORDS
• V$DIAG_SESS_SQL_TRACE_RECORDS
UTL_FILE UTL_FILE package

Details on what each operation does is contained in the SQL Reference Guide Under "PDB Lockdown Profile Features"

Note: It is likely that the "LOB_FILE_ACCCESS" operation, as listed by Oracle is misleading as I find no evidence of a CFILE data type
Managed Options
Option Name Operation
DATABASE_QUEUING Database operations associated with Advanced Queuing
PARTITIONING Creating partitioned tables and altering, or dropping partitions and subpartitions
Managed Statements
Statement Name Operation
ALTER DATABASE ALTER DATABASE DDL
ALTER PLUGGABLE DATABASE ALTER PLUGGABLE DATABASE DDL
ALTER SESSION ALTER SESSION DDL
ALTER SYSTEM ALTER SYSTEM DDL
Security Model To create and manage a Lockdown Profile an administrator must be granted the CREATE LOCKDOWN PROFILE system privilege. To enable a Lockdown Profile an administrator must be a common user with the ALTER SYSTEM system privilege or the common SYSDBA privilege.

The restrictions enforced by a lockdown profile are PDB-wide and affect all user including SYS and SYSTEM.

If a Lockdown Profile is created in CDB$ROOT it will apply globally to all PDBs in the CDB.

If a Lockdown Profile is created in an Application Root container it will apply to all Application PDBs under that application root.

If a Lockdown Profile is created within a PDB its scope will be limited to only that single PDB.

A single Pluggable Database (PDB) can have only a single Lockdown Profile.
 
Syntax
Create Lockdown Profile CREATE LOCKDOWN PROFILE <lockdown_profile_name [<FROM | INCLUDING> <base_profile_name>];
SQL> CREATE LOCKDOWN PROFILE dev_pdbs;
Alter Lockdown Profile Statement CREATE LOCKDOWN PROFILE <lockdown_profile_name>
<ENABLE | DISABLE> STATEMENT = ALL
[EXCEPT <(comma_delimited_statment_list')>];

CREATE LOCKDOWN PROFILE <lockdown_profile_name>
<ENABLE | DISABLE> STATEMENT = <('comma_delimited_statement_list')>;
[CLAUSE = <ALL [EXCEPT <(comma_delimited_statment_list')>]>];

CREATE LOCKDOWN PROFILE <lockdown_profile_name>
<ENABLE | DISABLE> STATEMENT = <('comma_delimited_statement_list')>;
[CLAUSE = <ALL [EXCEPT <(comma_delimited_statment_list')>]>]
[VALUE = <value_list | MINVALUE | MAXVALUE>];
SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE STATEMENT=('ALTER SYSTEM')
  3  CLAUSE=('SET')
  4* OPTION ALL EXCEPT = ('PLSQL_DEBUG, PLSQL_WARNINGS');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE STATEMENT = ('ALTER PLUGGABLE DATABASE')
  3  CLAUSE ALL EXCEPT = ('DEFAULT TABLESPACE', 'DEFAULT TEMPORARY TABLESPACE');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE STATEMENT = ('ALTER SESSION')
  3  CLAUSE = ('SET')
  4  OPTION = ('COMMIT_WAIT', 'CREATE STORED OUTLINES');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE STATEMENT = ('ALTER SYSTEM')
  3  CLAUSE = ('SET')
  4  OPTION = ('PDB_FILE_NAME_CONVERT')
  5  VALUE = ('hr_1_pdb1', 'csr_appcon3');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE STATEMENT = ('ALTER SYSTEM')
  3  CLAUSE = ('SET')
  4  OPTION = ('PARALLEL_THREADS_PER_CPU')
  5  MINVALUE = '1'
  6  MAXVALUE = '4';
Alter Lockdown Profile Feature CREATE LOCKDOWN PROFILE <lockdown_profile_name>
<ENABLE | DISABLE> FEATURE = ALL [EXCEPT <(comma_delimited_feature_list')>];

CREATE LOCKDOWN PROFILE <lockdown_profile_name>
<ENABLE | DISABLE> FEATURE = <('comma_delimited_feature_list')>;
SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE FEATURE=('NETWORK_ACCESS', 'OS_ACCESS', 'UTL_INADDR', 'UTL_TCP');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE FEATURE=ALL
  3  EXCEPT ('NETWORK_ACCESS');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE FEATURE=ALL;
Alter Lockdown Profile Option CREATE LOCKDOWN PROFILE <lockdown_profile_name>
<ENABLE | DISABLE> OPTION = ALL [EXCEPT <(comma_delimited_option_list')>];

CREATE LOCKDOWN PROFILE <lockdown_profile_name>
<ENABLE | DISABLE> OPTION = <('comma_delimited_option_list')>
SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE OPTION=('DATABASE QUEUING', 'PARTITIONING');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE OPTION=ALL
  3  EXCEPT = ('DATABASE QUEUING');

SQL> ALTER LOCKDOWN PROFILE dev_pdbs
  2  DISABLE OPTION=ALL;
Drop Lockdown Profile DROP LOCKDOWN PROFILE <lockdown_profile_name>;
SQL> DROP LOCKDOWN PROFILE dev_pdbs;
SQL> ALTER SYSTEM RESET pdb_lockdown SID='*' scope=BOTH;

Related Topics
System Privileges