ALTER LOCKDOWN PROFILE

Purpose

Use the ALTER LOCKDOWN PROFILE statement to alter a PDB lockdown profile. You can use PDB lockdown profiles in a multitenant environment to restrict user operations in pluggable databases (PDBs).

Immediately after you create a lockdown profile with the CREATE LOCKDOWN PROFILE statement, all user operations are enabled for the profile. You can then use the ALTER LOCKDOWN PROFILE statement to disable certain user operations for the profile. When a lockdown profile is applied to a CDB, application container, or PDB, users cannot perform the operations that are the disabled for the profile. If you later would like to reenable some of the disabled user operations, you can use the ALTER LOCKDOWN PROFILE statement to do so.

The ALTER LOCKDOWN PROFILE statement allows you to disable or enable:

See Also:

Prerequisites

Syntax

alter_lockdown_profile::=

Description of the illustration alter_lockdown_profile.eps

lockdown_features::=

Description of the illustration lockdown_features.eps

lockdown_options::=

Description of the illustration lockdown_options.eps

lockdown_statements::=

Description of the illustration lockdown_statements.eps

statement_clauses::=

Description of the illustration statement_clauses.eps

clause_options::=

Description of the illustration clause_options.eps

option_values::=

Description of the illustration option_values.eps

Semantics

profile_name

Specify the name of the PDB lockdown profile to be altered.

You can find the names of existing PDB lockdown profiles by querying the DBA_LOCKDOWN_PROFILES data dictionary view.

lockdown_features

This clause lets you disable or enable user operations associated with certain database features.

If you omit this clause, then the default is ENABLE ALL.

  <div class="infoboxnote" markdown="1">
  **Note:**
  - The Oracle Text type `FILE_DATASTORE` is deprecated. Oracle recommends that you replace `FILE_DATASTORE` indexes with the `DIRECTORY_DATASTORE` index type for greater security as it enables file access to be based on directory objects.

  - The Oracle Text type `URL_DATASTORE` is deprecated. Oracle recommeds that you replace `URL_DATASTORE` with `NETWORK_DATASTORE`, which uses ACLs to control access to specific servers.
  </div>

Table 1 PDB Lockdown Profile Features

Feature Bundle Feature Operations
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
COMMON_SCHEMA_ACCESS LOCAL_USER_COMMON_SCHEMA_ACCESS

  • A local user with an ANY system privilege (for example, CREATE ANY TABLE) creating or accessing objects in a common user's schema for which the privilege applies. Note: Disabling the LOCAL_USER_COMMON_SCHEMA_ACCESS feature does not prevent a local user with the SYSDBA privilege or specific object privileges from creating or accessing objects in a common user's schema. Therefore, Oracle recommends against granting such privileges to local users.

  • A local user with the BECOME USER system privilege becoming a common user

  • A local user altering a common user by issuing an ALTER USER statement

  • A local user using a common user for proxy connections
COMMON_SCHEMA_ACCESS SECURITY_POLICIES

Creation of certain 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 COMMON_USER_CONNECT A common user connecting to the PDB directly. If this feature is disabled, then in order to connect to the PDB, a common user must first connect to the CDB root and then switch to the desired PDB using the ALTER SESSION SET CONTAINER statement.
CONNECTIONS LOCAL_SYSOPER_RESTRICTED_MODE_CONNECT A local user with the SYSOPER privilege connecting to a PDB that is open in RESTRICTED mode
CTX_LOGGING CTX_LOGGING Use logging in Oracle Text PL/SQL procedures such as CTX_OUTPUT.START_LOG and CTX_OUTPUT.START_QUERY_LOG
JAVA JAVA Java as a whole. If this feature is disabled, then all options and features of the database that depend on Java will be disabled.
JAVA_RUNTIME JAVA_RUNTIME Operations through Java that require java.lang.RuntimePermission
NETWORK_ACCESS AQ_PROTOCOLS Using HTTP, SMTP, and OCI notification features.
NETWORK_ACCESS CTX_PROTOCOLS

  • Operations that access the Oracle Text datastore types DIRECTORY_DATASTORE and NETWORK_DATASTORE.

    The type DIRECTORY_DATASTORE has an attribute called DIRECTORY which is the directory object whose data is to be indexed. The default value of this attribute is null.

    The DIRECTORY_DATASTORE type replaces the FILE_DATASTORE type, which is deprecated.

    The NETWORK_DATASTORE type replaces the URL_DATASTORE type, which is deprecated.

    The type NETWORK_DATASTORE conforms to the standard database security model for providing URL access based on access control lists (ACLs), which support the HTTP and HTTPS protocols.

    The URL_DATASTORE type did not support HTTPS.

  • Printing tokens as part of CTX logging with events EVENT_INDEX_PRINT_TOKEN and EVENT_OPT_PRINT_TOKEN
NETWORK_ACCESS DBMS_DEBUG_JDWP Using the DBMS_DEBUG_JDWP PL/SQL package
NETWORK_ACCESS UTL_HTTP Using the UTL_HTTP PL/SQL package
NETWORK_ACCESS UTL_INADDR Using the UTL_INADDR PL/SQL package
NETWORK_ACCESS UTL_SMTP Using the UTL_SMTP PL/SQL package
NETWORK_ACCESS UTL_TCP Using the UTL_TCP PL/SQL package
NETWORK_ACCESS XDB_PROTOCOLS Using HTTP, FTP, and other network protocols through XDB
OS_ACCESS DROP_TABLESPACE_KEEP_DATAFILES Dropping a tablespace in the PDB without specifying the INCLUDING CONTENTS AND DATAFILES clause in DROP TABLESPACE statement
OS_ACCESS EXTERNAL_FILE_ACCESS Using external files or directory objects in the PDB when PATH_PREFIX is not set for the PDB
OS_ACCESS EXTERNAL_PROCEDURES Using external procedure agent extproc in the PDB
OS_ACCESS FILE_TRANSFER Using the DBMS_FILE_TRANSFER package
OS_ACCESS JAVA_OS_ACCESS Using java.io.FilePermission from Java
OS_ACCESS LOB_FILE_ACCESS Using BFILE and CFILE data types
OS_ACCESS TRACE_VIEW_ACCESS

Using 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
OS_ACCESS UTL_FILE Using UTL_FILE. If this feature is disabled, then the database blocks use of the UTL_FILE.FOPEN function.

lockdown_options

This clause lets you disable or enable user operations associate with certain database options.

If you omit this clause, then the default is ENABLE OPTION ALL.

lockdown_statements

This clause lets you disable or enable the issuance of certain SQL statements.

If you omit this clause, then the default is ENABLE STATEMENT ALL.

statement_clauses

This clause lets you disable or enable specific clauses of the specified SQL statement.

For clause, you must specify at least enough keywords to unambiguously identify a single clause for the SQL statement. The following are some examples of how to specify clause for the ALTER SYSTEM statement:

There is no need to specify optional keywords within a clause, because they have no effect. For example:

There is no need to specify parameter values within a clause, because they have no effect. For example:

clause_options

This clause is valid only when you specify one of the following for lockdown_statements and statement_clauses:

{ DISABLE | ENABLE } STATEMENT = ('ALTER SESSION') CLAUSE = ('SET')
{ DISABLE | ENABLE } STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET')

This clause lets you disable or enable the setting or modification of specific options with the ALTER SESSION SET or ALTER SYSTEM SET statements.

option_values

This clause is valid only when you specify one of the following for lockdown_statements, statement_clauses, and clause_options:

DISABLE STATEMENT = ('ALTER SESSION') CLAUSE = ('SET') OPTION = clause_option
DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET') OPTION = clause_option

This clause lets you specify a default value for an option when disabling the setting of that option. For options that take numeric values, this clause also lets you restrict users from setting an option to certain values.

Examples

The following statement creates PDB lockdown profile hr_prof:

CREATE LOCKDOWN PROFILE hr_prof;

The remaining examples in this section alter hr_prof.

Disabling Features for PDB Lockdown Profiles: Examples

The following statement disables all features in the feature bundle NETWORK_ACCESS:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE FEATURE = ('NETWORK_ACCESS');

The following statement disables the LOB_FILE_ACCESS and TRACE_VIEW ACCESS features:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE FEATURE = ('LOB_FILE_ACCESS', 'TRACE_VIEW_ACCESS');

The following statement disables all features except the COMMON_USER_LOCAL_SCHEMA_ACCESS and LOCAL_USER_COMMON_SCHEMA_ACCESS features:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE FEATURE ALL EXCEPT = ('COMMON_USER_LOCAL_SCHEMA_ACCESS', 'LOCAL_USER_COMMON_SCHEMA_ACCESS');

The following statement disables all features:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE FEATURE ALL;

Enabling Features for PDB Lockdown Profiles: Examples

The following statement enables the UTL_HTTP and UTL_SMTP features, as well as all features in the feature bundle OS_ACCESS:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE FEATURE = ('UTL_HTTP', 'UTL_SMTP', 'OS_ACCESS');

The following statement enables all features except the AQ_PROTOCOLS and CTX_PROTOCOLS features:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE FEATURE ALL EXCEPT = ('AQ_PROTOCOLS', 'CTX_PROTOCOLS');

The following statement enables all features:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE FEATURE ALL;

Disabling Options for PDB Lockdown Profiles: Examples

The following statement disables user operations associated with the Oracle Database Advanced Queuing option:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE OPTION = ('DATABASE QUEUING');

The following statement disables user operations associated with the Oracle Partitioning option:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE OPTION = ('PARTITIONING');

Enabling Options for PDB Lockdown Profiles: Examples

The following statement enables user operations associated with the Oracle Database Advanced Queuing option:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE OPTION = ('DATABASE QUEUING');

The following statement enables user operations associated both with the Oracle Database Advanced Queuing option and the Oracle Partitioning option:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE OPTION ALL;

Disabling SQL Statements for PBB Lockdown Profiles: Examples

The following statement disables the ALTER DATABASE statement:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER DATABASE');

The following statement disables the ALTER SYSTEM SUSPEND and ALTER SYSTEM RESUME statements:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER SYSTEM')
          CLAUSE = ('SUSPEND', 'RESUME');

The following statement disables all clauses of the ALTER PLUGGABLE DATABASE statement, except DEFAULT TABLESPACE and DEFAULT TEMPORARY TABLESPACE:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER PLUGGABLE DATABASE')
          CLAUSE ALL EXCEPT = ('DEFAULT TABLESPACE', 'DEFAULT TEMPORARY TABLESPACE');

The following statement disables using the ALTER SESSION statement to set or modify COMMIT_WAIT or CURSOR_SHARING:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER SESSION')
          CLAUSE = ('SET')
          OPTION = ('COMMIT_WAIT', 'CURSOR_SHARING');

The following statement disables using the ALTER SYSTEM statement to set or modify the value of PDB_FILE_NAME_CONVERT. It also sets the default value for PDB_FILE_NAME_CONVERT to 'cdb1_pdb0', 'cdb1_pdb1'. This default value will take effect the next time the PDB is closed and reopened.

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER SYSTEM')
          CLAUSE = ('SET')
          OPTION = ('PDB_FILE_NAME_CONVERT')
          VALUE = ('cdb1_pdb0', 'cdb1_pdb1');

The following statement disables using the ALTER SYSTEM statement to set or modify the value of CPU_COUNT to a value less than 8:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER SYSTEM')
          CLAUSE = ('SET')
          OPTION = ('CPU_COUNT')
          MINVALUE = '8';

The following statement disables using the ALTER SYSTEM statement to set or modify the value of CPU_COUNT to a value greater than 2:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER SYSTEM')
          CLAUSE = ('SET')
          OPTION = ('CPU_COUNT')
          MAXVALUE = '2';

The following statement disables using the ALTER SYSTEM statement to set or modify the value of CPU_COUNT to a value less than 2 or greater than 6:

ALTER LOCKDOWN PROFILE hr_prof
  DISABLE STATEMENT = ('ALTER SYSTEM')
          CLAUSE = ('SET')
          OPTION = ('CPU_COUNT')
          MINVALUE = '2'
          MAXVALUE = '6';

Enabling SQL Statements for PBB Lockdown Profiles: Examples

The following statement enables all statements except ALTER DATABASE:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE STATEMENT ALL EXCEPT = ('ALTER DATABASE');

The following statement enables the ALTER DATABASE MOUNT and ALTER DATABASE OPEN statements:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE STATEMENT = ('ALTER DATABASE')
          CLAUSE = ('MOUNT', 'OPEN');

The following statement enables all clauses of the ALTER PLUGGABLE DATABASE statement, except DEFAULT TABLESPACE and DEFAULT TEMPORARY TABLESPACE:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE STATEMENT = ('ALTER PLUGGABLE DATABASE')
         CLAUSE ALL EXCEPT = ('DEFAULT TABLESPACE', 'DEFAULT TEMPORARY TABLESPACE');

The following statement enables using the ALTER SESSION statement to set or modify COMMIT_WAIT or CURSOR_SHARING:

ALTER LOCKDOWN PROFILE hr_prof
  ENABLE STATEMENT = ('ALTER SESSION')
         CLAUSE = ('SET')
         OPTION = ('COMMIT_WAIT', 'CURSOR_SHARING');