Purpose:
LOCKDOWN Profiles concept will be applicable for Oracle Multitenant databases. We can apply this LOCKDOWN Profiles concept to specific CDB, specific PDB or ALL PDBs. This Concept will help database administrators to provide database security by restricting unprivileged user access. Unprivileged users can’t able to perform system level queries like “ALTER SYSTEM”, “ALTER DATABASE”, “ALTER PLUGGABLE_DATABASE” etc or OS Level (OS_ACCESS) operations or Network Level (UTL_HTTP) Operations based on the Lockdown profile option we did created.
PDB lockdown profiles restrict user access to the functionality the features provided, similar to resource limits that are defined for users. As the name suggests, you use PDB lockdown profiles in a CDB, for an application container, or for a PDB or application PDB.
Default LOCKDOWN PROFILES
Oracle Database provides a set of empty default PDB lockdown. Those default lockdown profiles act as templates for creating custom profiles.
PRIVATE_DBAAS With this Template, We can insist restrictions suitable for private Cloud Database-as-a-Service (DBaaS) deployments.
SAAS With this Template, We can insist restrictions suitable for Software-as-a-Service (SaaS) deployments.
PUBLIC_DBAAS With this Template, We can insist restrictions suitable for public Cloud Database-as-a-Service (DBaaS) deployments.
- Privileges required to perform profile lockdown.
SQL> show user USER is "SYS" SQL> create user c##cdbadmin identified by Oracle123; User created. SQL> grant create session to C##CDBADMIN; Grant succeeded. SQL> grant create lockdown profile,alter lockdown profile,drop lockdown profile to C##CDBADMIN; Grant succeeded. SQL> conn c##cdbadmin/Oracle123 Connected. SQL> grant select on DBA_LOCKDOWN_PROFILES to C##CDBADMIN; Grant succeeded. SQL> grant select on CDB_LOCKDOWN_PROFILES to C##CDBADMIN; Grant succeeded. SQL> conn c##cdbadmin/Oracle123 Connected. SQL> select count(*) from DBA_LOCKDOWN_PROFILES ; COUNT(*) ---------- 3
- Data Dictionary Views to query info about Lockdown Profiles
select * from DBA_LOCKDOWN_PROFILES; select * from CDB_LOCKDOWN_PROFILES; SQL> col PROFILE_NAME for a20 SQL> set linesize 200 SQL> select PROFILE_NAME,STATUS,USERS from DBA_LOCKDOWN_PROFILES; PROFILE_NAME STATUS USERS -------------------- ------- ------ PRIVATE_DBAAS EMPTY PUBLIC_DBAAS EMPTY SAAS EMPTY
To Verify Lockdown profiles at PDB Level, use below query.
select * from v$lockdown_rules;
- CREATE and ALTER lockdown profiles
SQL> show user
USER is "C##CDBADMIN"
SQL> CREATE LOCKDOWN PROFILE pdb_secure_prof INCLUDING PRIVATE_DBAAS;
Lockdown Profile created.
SQL> ALTER LOCKDOWN PROFILE pdb_secure_prof DISABLE STATEMENT = ('ALTER SYSTEM');
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE pdb_secure_prof ENABLE STATEMENT = ('ALTER SYSTEM') clause = ('flush shared_pool');
Lockdown Profile altered.
Testcase-PreValidation before lockdown profile enable:
SQL> conn / as sysdba Connected. SQL> alter session set container=pdb; Session altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB READ WRITE NO SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_ORCL19C SQL> alter system set local_listener='LISTENER_ORCL19C' scope=both; System altered. SQL> alter system flush shared_pool; System altered.
- Enables the lockdown profile named pdb_secure_proffor all PDBs:
SQL> conn / as sysdba Connected. SQL> ALTER SYSTEM SET PDB_LOCKDOWN = pdb_secure_prof; System altered. SQL> SET LINESIZE 150 SQL> COL PROFILE_NAME FORMAT a20 SQL> COL RULE FORMAT a20 SQL> COL CLAUSE FORMAT a25 SQL> SELECT PROFILE_NAME, RULE, CLAUSE, STATUS FROM CDB_LOCKDOWN_PROFILES; PROFILE_NAME RULE CLAUSE STATUS -------------------- -------------------- ------------------------- ------- PDB_SECURE_PROF ALTER SYSTEM DISABLE PDB_SECURE_PROF ALTER SYSTEM FLUSH SHARED_POOL ENABLE PRIVATE_DBAAS EMPTY PUBLIC_DBAAS EMPTY SAAS EMPTY
Reset lockdown Profile:
ALTER SYSTEM RESET PDB_LOCKDOWN;
Testcase- Post Validation
SQL> alter session set container=PDB; Session altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB READ WRITE NO SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_ORCL19C SQL> alter system set local_listener='LISTENER_ORCL19C' scope=both; alter system set local_listener='LISTENER_ORCL19C' scope=both * ERROR at line 1: ORA-01031: insufficient privileges SQL> alter system flush shared_pool; System altered. SQL>
- Disable Lockdown Profile:
ALTER SYSTEM SET PDB_LOCKDOWN = ' ' SCOPE = BOTH;
- Drop Lockdown Profile:
DROP LOCKDOWN PROFILE pdb_secure_prof;
- How to check LOCKDOWN PROFILE enabled for which PDBs:
SQL> conn / as sysdba Connected. SQL> select con_id, name, value from gv$system_parameter where name='pdb_lockdown'; CON_ID NAME VALUE ---------- -------------------- ------------------------------ 0 pdb_lockdown HR_PROF SQL> alter session set container=PDB; Session altered. SQL> alter system set pdb_lockdown='HR_PROF'; System altered. SQL> select con_id, name, value from gv$system_parameter where name='pdb_lockdown'; CON_ID NAME VALUE ---------- -------------------- ------------------------------ 3 pdb_lockdown HR_PROF SQL> conn / as sysdba Connected. SQL> select con_id, name, value from gv$system_parameter where name='pdb_lockdown'; CON_ID NAME VALUE ---------- -------------------- ------------------------------ 0 pdb_lockdown HR_PROF 3 pdb_lockdown HR_PROF
- Examples:
SQL> show user
USER is "C##CDBADMIN"
SQL> ALTER LOCKDOWN PROFILE HR_PROF DISABLE FEATURE = ('LOB_FILE_ACCESS', 'TRACE_VIEW_ACCESS');
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF DISABLE FEATURE ALL EXCEPT = ('COMMON_USER_LOCAL_SCHEMA_ACCESS', 'LOCAL_USER_COMMON_SCHEMA_ACCESS');
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF ENABLE FEATURE = ('UTL_HTTP', 'UTL_SMTP', 'OS_ACCESS');
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SUSPEND', 'RESUME');
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF DISABLE STATEMENT = ('ALTER PLUGGABLE DATABASE') CLAUSE ALL EXCEPT = ('DEFAULT TABLESPACE', 'DEFAULT TEMPORARY TABLESPACE');
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET') OPTION = ('CPU_COUNT') MINVALUE = '8';
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET') OPTION = ('CPU_COUNT') MAXVALUE = '2';
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET') OPTION = ('CPU_COUNT') MINVALUE = '2' MAXVALUE = '6';
Lockdown Profile altered.
SQL> ALTER LOCKDOWN PROFILE HR_PROF ENABLE STATEMENT = ('ALTER DATABASE') CLAUSE = ('MOUNT', 'OPEN');
Lockdown Profile altered.
Conclusion: Above steps will helps to protect database from changes happening to database by the PDB users or PDB administrators.
