Infrastructure at your Service

Franck Pachot

ADWC – System and session settings (DWCS lockdown profile)

By May 24, 2018 Oracle No Comments

By Franck Pachot

.
The Autonomous Data Warehouse Cloud service is a PaaS managed service where we have a PDB and an ADMIN user which has most of the system privileges. For example, we have the privilege to change initialization parameters:

SQL> select * from dba_sys_privs where grantee=user and privilege like 'ALTER S%';
 
GRANTEE   PRIVILEGE       ADMIN_OPTION   COMMON   INHERITED   
-------   ---------       ------------   ------   ---------
ADMIN     ALTER SESSION   YES            NO       NO          
ADMIN     ALTER SYSTEM    YES            NO       NO  

Still, not everything is allowed for several reasons: ensure that we cannot break the Oracle managed CDB and force us to use only the features allowed in the ‘autonomous’ service. This is limited with a lockdown profile:

SQL> show parameter pdb_lockdown
 
NAME         TYPE   VALUE 
------------ ------ ----- 
pdb_lockdown string DWCS  

DWCS means Data Warehouse Cloud Service which was the name of the Autonomous Data Warehouse Cloud service until Larry Ellison announces this self-driven-no-human trend under the marketing umbrella of ‘autonomous’.

The limitations are all documented but I like to see them by myself and in 18c we have a mean to see the lockdown rules from the PDB itself, through V$LOCKDOWN_RULES.

ALTER SYSTEM

Basically, in this ADWC all ALTER SYSTEM statements are disallowed and then they add the few exceptions for what we are allowed to:


SQL> select * from v$lockdown_rules where rule in ('ALTER SYSTEM') and clause_option is null;
RULE_TYPE   RULE           CLAUSE         CLAUSE_OPTION   STATUS    USERS    CON_ID   
---------   ----           ------         -------------   ------    -----    ------
STATEMENT   ALTER SYSTEM                                  DISABLE   ALL            73 
STATEMENT   ALTER SYSTEM   SET                            ENABLE    COMMON         73 
STATEMENT   ALTER SYSTEM   KILL SESSION                   ENABLE    ALL            73 

You can ignore what is enabled for COMMON users because we have no common user to connect to our PDB. We will see which ALTER SYSTEM SET clauses are allowed. But in addition to those, only the ‘KILL SESSION’ is allowed for ALTER SYSTEM.

Here is the detail about the parameters we can set:


SQL> select * from v$lockdown_rules left outer join (select upper(name) clause_option,display_value,description from v$parameter) using (clause_option) where rule in ('ALTER SYSTEM') and clause_option is not null and status='ENABLE';
 
CLAUSE_OPTION                     RULE_TYPE   RULE           CLAUSE   STATUS   USERS   CON_ID   DISPLAY_VALUE            DESCRIPTION                                                                                                                                   
-------------                     ---------   ----           ------   ------   -----   ------   -------------            -----------                                                                                                         
APPROX_FOR_AGGREGATION            STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 FALSE                    Replace exact aggregation with approximate aggregation                                                                                        
APPROX_FOR_COUNT_DISTINCT         STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 FALSE                    Replace count distinct with approx_count_distinct                                                                                             
APPROX_FOR_PERCENTILE             STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 none                     Replace percentile_* with approx_percentile                                                                                                   
AWR_PDB_AUTOFLUSH_ENABLED         STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 TRUE                     Enable/Disable AWR automatic PDB flushing                                                                                                     
NLS_LANGUAGE                      STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 AMERICAN                 NLS language name                                                                                                                             
NLS_SORT                          STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          NLS linguistic definition name                                                                                                                
NLS_TERRITORY                     STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 AMERICA                  NLS territory name                                                                                                                            
NLS_CALENDAR                      STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          NLS calendar system name                                                                                                                      
NLS_COMP                          STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 BINARY                   NLS comparison                                                                                                                                
NLS_CURRENCY                      STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          NLS local currency symbol                                                                                                                     
NLS_DATE_FORMAT                   STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 DD-MON-YYYY HH24:MI:ss   NLS Oracle date format                                                                                                                        
NLS_DATE_LANGUAGE                 STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          NLS date language name                                                                                                                        
NLS_DUAL_CURRENCY                 STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          Dual currency symbol                                                                                                                          
NLS_ISO_CURRENCY                  STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          NLS ISO currency territory name                                                                                                               
NLS_LENGTH_SEMANTICS              STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 BYTE                     create columns using byte or char semantics by default                                                                                        
NLS_NCHAR_CONV_EXCP               STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 FALSE                    NLS raise an exception instead of allowing implicit conversion                                                                                
NLS_NUMERIC_CHARACTERS            STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          NLS numeric characters                                                                                                                        
NLS_TIMESTAMP_FORMAT              STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          time stamp format                                                                                                                             
NLS_TIMESTAMP_TZ_FORMAT           STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          timestamp with timezone format                                                                                                                
NLS_TIME_FORMAT                   STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          time format                                                                                                                                   
NLS_TIME_TZ_FORMAT                STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          time with timezone format                                                                                                                     
OPTIMIZER_IGNORE_HINTS            STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 TRUE                     enables the embedded hints to be ignored                                                                                                      
OPTIMIZER_IGNORE_PARALLEL_HINTS   STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 TRUE                     enables embedded parallel hints to be ignored                                                                                                 
PLSCOPE_SETTINGS                  STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 identifiers:all          plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL source code identifier and SQL statement data   
PLSQL_CCFLAGS                     STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73                          PL/SQL ccflags                                                                                                                                
PLSQL_DEBUG                       STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 FALSE                    PL/SQL debug                                                                                                                                  
PLSQL_OPTIMIZE_LEVEL              STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 1                        PL/SQL optimize level                                                                                                                         
PLSQL_WARNINGS                    STATEMENT   ALTER SYSTEM   SET      ENABLE   ALL           73 DISABLE:ALL              PL/SQL compiler warnings settings                                                                                                             

The APPROX_ ones, disable by default, can be used to transparently use approximations for faster results.
The NLS_ ones can be used to set NLS defaults for our sessions.
OPTIMIZER_IGNORE_ are new in 18c and are set by default here to ignore embedded hints. However, we can set then to false.
PLSQL_ are the defaults for sessions and I don’t understand why warnings are not enabled by default. Fortunately, we are able to change that at PDB level.

There are also some rules to disable some ALTER SYSTEM SET. They are there for the common users only (which have ALTER SYSTEM SET enabled) but they are interesting to see what Oracle choose to set in the ADWC service which cannot be changed in the PDB even by their common users:


SQL> select * from v$lockdown_rules left outer join (select upper(name) clause_option,display_value,description from v$parameter) using (clause_option) where rule in ('ALTER SYSTEM') and clause_option is not null and status='DISABLE';
CLAUSE_OPTION                            RULE_TYPE   RULE           CLAUSE   STATUS    USERS   CON_ID   DISPLAY_VALUE     DESCRIPTION                                                                                                                                                                   
-------------                            ---------   ----           ------   ------    -----   ------   -------------     -----------                                                                                                        
DB_FILES                                 STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 25                max allowable # db files                                                                                                                                                      
"_PDB_INHERIT_CFD"                       STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73                                                                                                                                                                                                 
"_PDB_MAX_AUDIT_SIZE"                    STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73                                                                                                                                                                                                 
"_PDB_MAX_DIAG_SIZE"                     STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73                                                                                                                                                                                                 
MAX_IDLE_TIME                            STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 60                maximum session idle time in minutes                                                                                                                                          
PARALLEL_DEGREE_POLICY                   STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 AUTO              policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE)                                                                                               
_PARALLEL_CLUSTER_CACHE_POLICY           STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 ADAPTIVE          policy used for parallel execution on cluster(ADAPTIVE/CACHED)                                                                                                                
_ENABLE_PARALLEL_DML                     STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 TRUE              enables or disables parallel dml                                                                                                                                              
RESULT_CACHE_MODE                        STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 FORCE             result cache operator usage mode                                                                                                                                              
RESULT_CACHE_MAX_RESULT                  STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 1                 maximum result size as percent of cache size                                                                                                                                  
RESOURCE_MANAGER_PLAN                    STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 FORCE:DWCS_PLAN   resource mgr top plan                                                                                                                                                         
_CELL_OFFLOAD_VECTOR_GROUPBY             STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 FALSE             enable SQL processing offload of vector group by                                                                                                                              
PARALLEL_MIN_DEGREE                      STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 CPU               controls the minimum DOP computed by Auto DOP                                                                                                                                 
_MAX_IO_SIZE                             STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 33554432          Maximum I/O size in bytes for sequential file accesses                                                                                                                        
_LDR_IO_SIZE                             STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 33554432          size of write IOs used during a load operation                                                                                                                                
_LDR_IO_SIZE2                            STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 33554432          size of write IOs used during a load operation of EHCC with HWMB                                                                                                              
_OPTIMIZER_GATHER_STATS_ON_LOAD_ALL      STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 TRUE              enable/disable online statistics gathering for nonempty segments                                                                                                              
_OPTIMIZER_GATHER_STATS_ON_LOAD_HIST     STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 TRUE              enable/disable online histogram gathering for loads                                                                                                                           
_DATAPUMP_GATHER_STATS_ON_LOAD           STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 TRUE              Gather table statistics during Data Pump load rather thanimporting statistics from the dump file. This should be set to TRUE in the lockdown profile in a DWCS environment.   
_OPTIMIZER_ANSWERING_QUERY_USING_STATS   STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 TRUE              enable statistics-based query transformation                                                                                                                                  
_PX_XTGRANULE_SIZE                       STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 128000            default size of a external table granule (in KB)                                                                                                                              
_OPTIMIZER_ALLOW_ALL_ACCESS_PATHS        STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 FALSE             allow all access paths                                                                                                                                                        
_DATAPUMP_INHERIT_SVCNAME                STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 TRUE              Inherit and propagate service name throughout job                                                                                                                             
_DEFAULT_PCT_FREE                        STATEMENT   ALTER SYSTEM   SET      DISABLE   ALL           73 1                 Default value of PCT_FREE enforced by DWCS lockdown                                                                                                                           

So, among the interesting ones, Result Cache is forced for all results (RESULT_CACHE_MODE=FORCE), Parallel DML is enabled for all sessions (but we will see that we can disable it at session level), PCTFREE will always be 1 (_DEFAULT_PCT_FREE=1), statistics are gathered during load (this is a 18c feature). And we cannot change that.

There are only few additional ALTER SYSTEM SET which are allowed at session level:


SQL> select * from v$lockdown_rules where rule in ('ALTER SESSION') and clause is not null and clause_option is not null
     and (clause_option,status,users) not in (select clause_option,status,users from v$lockdown_rules where rule in ('ALTER SYSTEM') and clause is not null and clause_option is not null)
     ;
RULE_TYPE   RULE            CLAUSE   CLAUSE_OPTION                          STATUS   USERS   CON_ID   
---------   ----            ------   -------------                          ------   -----   ------
STATEMENT   ALTER SESSION   SET      CONTAINER                              ENABLE   ALL           73 
STATEMENT   ALTER SESSION   SET      CURRENT_SCHEMA                         ENABLE   ALL           73 
STATEMENT   ALTER SESSION   SET      EDITION                                ENABLE   ALL           73 
STATEMENT   ALTER SESSION   SET      OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES   ENABLE   ALL           73 
STATEMENT   ALTER SESSION   SET      DEFAULT_COLLATION                      ENABLE   ALL           73 
STATEMENT   ALTER SESSION   SET      ROW ARCHIVAL VISIBILITY = ACTIVE       ENABLE   ALL           73 
STATEMENT   ALTER SESSION   SET      ROW ARCHIVAL VISIBILITY = ALL          ENABLE   ALL           73 
STATEMENT   ALTER SESSION   SET      TIME_ZONE                              ENABLE   ALL           73 

Besides the parameters here are what we can do with ALTER SESSION:


SQL> select * from v$lockdown_rules where rule='ALTER SESSION' and clause_option is null;
 
RULE_TYPE   RULE            CLAUSE                        CLAUSE_OPTION   STATUS    USERS    CON_ID  
---------   ----            ------                        -------------   ------    -----    ------ 
STATEMENT   ALTER SESSION                                                 DISABLE   ALL            73 
STATEMENT   ALTER SESSION   SET                                           ENABLE    COMMON         73 
STATEMENT   ALTER SESSION   ADVISE COMMIT                                 ENABLE    ALL            73 
STATEMENT   ALTER SESSION   CLOSE DATABASE LINK                           ENABLE    ALL            73 
STATEMENT   ALTER SESSION   DISABLE COMMIT IN PROCEDURE                   ENABLE    ALL            73 
STATEMENT   ALTER SESSION   DISABLE PARALLEL DDL                          ENABLE    ALL            73 
STATEMENT   ALTER SESSION   DISABLE PARALLEL DML                          ENABLE    ALL            73 
STATEMENT   ALTER SESSION   DISABLE PARALLEL QUERY                        ENABLE    ALL            73 
STATEMENT   ALTER SESSION   DISABLE RESUMABLE                             ENABLE    ALL            73 
STATEMENT   ALTER SESSION   ENABLE COMMIT IN PROCEDURE                    ENABLE    ALL            73 
STATEMENT   ALTER SESSION   ENABLE PARALLEL DDL                           ENABLE    ALL            73 
STATEMENT   ALTER SESSION   ENABLE PARALLEL DML                           ENABLE    ALL            73 
STATEMENT   ALTER SESSION   ENABLE PARALLEL QUERY                         ENABLE    ALL            73 
STATEMENT   ALTER SESSION   ENABLE RESUMABLE                              ENABLE    ALL            73 
STATEMENT   ALTER SESSION   FORCE PARALLEL DDL                            ENABLE    ALL            73 
STATEMENT   ALTER SESSION   FORCE PARALLEL DML                            ENABLE    ALL            73 
STATEMENT   ALTER SESSION   FORCE PARALLEL QUERY                          ENABLE    ALL            73 

I’ll show other rules (other than ALTER SYSTEM and ALTER SESSION statements) in a future post. Lockdown profiles is a great feature because they have very fine granularity and makes it easy to document what is allowed or not. Oracle introduced them for their own usage in the public cloud. You can use the same on-premises for your private cloud. This requires multitenant architecture, but the option is not mandatory.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist