oracle 12c及18c PDB 新特性 lockdown profile 例子

郑全

有权限，但不想让其做的一个功能，只是这个功能只能在PDB中实现，比较可惜。
该功能从12.2开始引入，只有静态LOCKDOWN PROFILE,到18c开始，增加了动态lockdown profile.
下面是一个完整的例子：

--1 create pdb

--1.1.create env
[oracle@dbserver admin]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle


mkdir -p /u01/app/oracle/oradata/CDB2/hr_root
mkdir -p /u01/app/oracle/oradata/CDB2/hr_root/operations
mkdir -p /u01/app/oracle/oradata/CDB2/hr_root/sales


--create network service alias

cat $ORACLE_HOME/netwok/admin/tnsnames.ora

hr_root =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hr_root)
    )
  )


operations =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = operations)
    )
  )

sales =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sales)
    )
  )



--1.2.create hr_root

ALTER PLUGGABLE DATABASE hr_root CLOSE;
DROP PLUGGABLE DATABASE sales INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE operations INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE hr_root INCLUDING DATAFILES;

ALTER SESSION SET db_create_file_dest='/u01/app/oracle/oradata/CDB2/hr_root';
CREATE PLUGGABLE DATABASE hr_root AS APPLICATION CONTAINER
  ADMIN USER admin IDENTIFIED BY password  ROLES=(CONNECT)
        CREATE_FILE_DEST='/u01/app/oracle/oradata/CDB2/hr_root';
alter PLUGGABLE DATABASE hr_root open;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HR_ROOT                        READ WRITE NO

--1.3.create hr-app
conn sys/sztech_4U@hr_root as sysdba;

ALTER PLUGGABLE DATABASE APPLICATION hr_app begin install '1.0';
create user hr_mgr identified by sztech_4U container=all;
grant create session, dba to hr_mgr container=all;
DROP USER hr_lock_mgr CASCADE;
CREATE USER hr_lock_mgr IDENTIFIED BY sztech_4U CONTAINER=ALL;
GRANT create session, alter system, select any dictionary,
      create any table, drop any table,
      create lockdown profile, alter lockdown profile, drop lockdown profile
          TO hr_lock_mgr CONTAINER=ALL;

ALTER PLUGGABLE DATABASE APPLICATION hr_app end install '1.0';


--1.4.create operations app pdb
conn sys/sztech_4U@hr_root as sysdba;
CREATE PLUGGABLE DATABASE operations ADMIN USER admin IDENTIFIED BY password  ROLES=(CONNECT)
    CREATE_FILE_DEST='/u01/app/oracle/oradata/CDB2/hr_root/operations';
alter pluggable database operations open;

--1.5.create sales app pdb

conn sys/sztech_4U@hr_root as sysdba;
CREATE PLUGGABLE DATABASE sales ADMIN USER admin IDENTIFIED BY password  ROLES=(CONNECT)
    CREATE_FILE_DEST='/u01/app/oracle/oradata/CDB2/hr_root/sales';
alter pluggable database sales open;

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 HR_ROOT                        READ WRITE NO
         4 OPERATIONS                     READ WRITE NO
         5 SALES                          READ WRITE NO

--1.6.sync app
CONNECT sys/sztech_4U@operations AS SYSDBA
ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;
CONNECT sys/sztech_4U@sales AS SYSDBA
ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;


--2.Create a Base Lockdown Profile at hr_root app root
--2.1 Create the app_root_prof lockdown profile
  conn  hr_lock_mgr/sztech_4U@HR_ROOT
  DROP LOCKDOWN PROFILE app_root_prof;
  CREATE LOCKDOWN PROFILE app_root_prof;
  ALTER LOCKDOWN PROFILE app_root_prof
               DISABLE STATEMENT = ('alter system');

  ALTER LOCKDOWN PROFILE app_root_prof
               ENABLE STATEMENT = ('alter system')
               CLAUSE = ('flush shared_pool','set');

--2.2 Set the app_root_prof lockdown profile in the HR_ROOT application root.
  ALTER SYSTEM SET pdb_lockdown = app_root_prof SCOPE = both;

--2.3 Verify that the lockdown profile was created in the application root
  SHOW PARAMETER pdb_lockdown


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      APP_ROOT_PROF
SQL>

--3.Create and Set a Static Lockdown Profile
--3.1 For the SALES application PDB in the application root, create the static_sales_prof static application root lockdown profile from the app_root_prof application root lockdown profile.
  conn  hr_lock_mgr/sztech_4U@HR_ROOT
  CREATE LOCKDOWN PROFILE static_sales_prof FROM app_root_prof;

--3.2 Update static_sales_prof to disallow the ALTER PLUGGABLE DATABASE command
ALTER LOCKDOWN PROFILE static_sales_prof
               DISABLE STATEMENT = ('alter pluggable database');


--3.3 Display the static lockdown profile. Read the list from code1 for typical output
  SQL> col PROFILE_NAME for a20
  SQL> col rule for a20
  SQL> col CLAUSE for a20

  SELECT profile_name, rule, clause, status FROM cdb_lockdown_profiles;

PROFILE_NAME         RULE                           CLAUSE               STATUS
-------------------- ------------------------------ -------------------- -------
APP_ROOT_PROF        ALTER SYSTEM                                        DISABLE
APP_ROOT_PROF        ALTER SYSTEM                   FLUSH SHARED_POOL    ENABLE
APP_ROOT_PROF        ALTER SYSTEM                   SET                  ENABLE
STATIC_SALES_PROF    ALTER PLUGGABLE DATABASE                            DISABLE
STATIC_SALES_PROF    ALTER SYSTEM                                        DISABLE
STATIC_SALES_PROF    ALTER SYSTEM                   FLUSH SHARED_POOL    ENABLE
STATIC_SALES_PROF    ALTER SYSTEM                   SET                  ENABLE

7 rows selected.

--The rules from the base app_root_prof lockdown profile are copied into the static_sales_prof lockdown profile.

--3.3 Log in to the SALES application PDB
CONNECT hr_lock_mgr/sztech_4U@SALES

--3.4 Set the static_sales_prof lockdown profile
ALTER SYSTEM SET pdb_lockdown = static_sales_prof SCOPE = both;

--3.5 Verify that the static lockdown profile is set in SALES.
SQL> SHOW PARAMETER pdb_lockdown

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      STATIC_SALES_PROF
SQL>


--4.Create and Set a Dynamic Lockdown Profile
--4.1 Log in to the application PDB OPERATIONS
  CONNECT hr_lock_mgr/sztech_4U@OPERATIONS

--4.2 Create a partitioned table.
      --The purpose of this step is to verify that you can still create a partitioned table,
       --because the restriction rule of the dynamic lockdown profile is the partitioning feature.
CREATE TABLE sales ( SALESMAN_ID  NUMBER(5),
            SALESMAN_NAME VARCHAR2(30), SALES_STATE VARCHAR2(20))
            PARTITION BY LIST (SALES_STATE) AUTOMATIC
           (PARTITION P_CAL VALUES ('CALIFORNIA'));

--4.3 Drop the table
  DROP TABLE sales;

--4.4 Log in to the application root
  CONNECT hr_lock_mgr/sztech_4U@HR_ROOT

--4.5 For the OPERATIONS application PDB in the application root, create the dynamic_op_prof dynamic application root lockdown profile from the app_root_prof application root lockdown profile.
  CREATE LOCKDOWN PROFILE dynamic_op_prof INCLUDING app_root_prof;

--4.6 Update dynamic_op_prof to disallow the partitioning feature
ALTER LOCKDOWN PROFILE dynamic_op_prof DISABLE OPTION = ('PARTITIONING');

--4.7 Display the dynamic lockdown profile. Read from the list in code2 for typical outpu


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

PROFILE_NAME         RULE                 CLAUSE               STATUS
-------------------- -------------------- -------------------- -------
APP_ROOT_PROF        ALTER SYSTEM                              DISABLE
APP_ROOT_PROF        ALTER SYSTEM         FLUSH SHARED_POOL    ENABLE
APP_ROOT_PROF        ALTER SYSTEM         SET                  ENABLE
DYNAMIC_OP_PROF      PARTITIONING                              DISABLE
STATIC_SALES_PROF    ALTER SYSTEM                              DISABLE
STATIC_SALES_PROF    ALTER SYSTEM         FLUSH SHARED_POOL    ENABLE
STATIC_SALES_PROF    ALTER SYSTEM         SET                  ENABLE

7 rows selected.

--The rules from the base app_root_prof lockdown profile aren't copied into the dynamic_op_prof lockdown profile. You will observe that, however, they are effective.

--4.8 Log in to the OPERATIONS application PDB
CONNECT hr_lock_mgr/sztech_4U@OPERATIONS

--4.9 Set the dynamic_op_prof dynamic lockdown profile
  ALTER SYSTEM SET pdb_lockdown = dynamic_op_prof SCOPE = both;

--4.10 Verify that the static lockdown profile is set in OPERATIONS.
SQL> SHOW PARAMETER pdb_lockdown

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DYNAMIC_OP_PROF
SQL>

--5.Execute Commands Allowed and Disallowed by the Static Lockdown Profile

--5.1 Log in to the SALES application PDB
CONNECT hr_lock_mgr/sztech_4U@SALES


--5.2 Execute the ALTER SYSTEM SET command that sets DDL_LOCK_TIMEOUT to 30 seconds. The command respects the rule inherited from app_root_prof.
SQL> ALTER SYSTEM SET ddl_lock_timeout=30 scope=both;

System altered.


--5.3 Execute the ALTER SYSTEM FLUSH SHARED_POOL command that flushes the shared pool. The command respects the rule inherited from app_root_prof.


SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

--5.4 Execute the ALTER SYSTEM CHECKPOINT command that completes a checkpoint between the database buffer cache and the datafiles. The command respects the rule inherited from app_root_prof.

SQL> ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM CHECKPOINT
*
ERROR at line 1:
ORA-01031: insufficient privileges

--5.5 Execute the ALTER PLUGGABLE DATABASE command that alters the PDB by closing it in this case. The command respects rules inherited from static_sales_prof.
SQL> ALTER PLUGGABLE DATABASE CLOSE;
ALTER PLUGGABLE DATABASE CLOSE
*
ERROR at line 1:
ORA-01031: insufficient privileges


--5.6 Execute the CREATE TABLE ... PARTITION command that creates a partitioned table requiring the partitioning option enabled. There is no restriction in static_sales_prof for this command to execute.
CREATE TABLE sales
      ( SALESMAN_ID  NUMBER(5), SALESMAN_NAME VARCHAR2(30),
        SALES_STATE VARCHAR2(20))
       PARTITION BY LIST (SALES_STATE) AUTOMATIC
        (PARTITION P_CAL VALUES ('CALIFORNIA'));
SQL> CREATE TABLE sales
  2        ( SALESMAN_ID  NUMBER(5), SALESMAN_NAME VARCHAR2(30),
  3          SALES_STATE VARCHAR2(20))
  4         PARTITION BY LIST (SALES_STATE) AUTOMATIC
  5          (PARTITION P_CAL VALUES ('CALIFORNIA'));

Table created.

SQL>

--6.Execute Commands Allowed and Disallowed by the Dynamic Lockdown Profile

--6.1 Log in to the OPERATIONS application PDB
CONNECT hr_lock_mgr/sztech_4U@OPERATIONS

--6.2 execute the ALTER SYSTEM SET command that sets DDL_LOCK_TIMEOUT to 30 seconds. The command respects the rule inherited from app_root_prof.
SQL> ALTER SYSTEM SET ddl_lock_timeout=30 scope=both;

System altered.

--6.3 Execute the ALTER SYSTEM FLUSH SHARED_POOL command that flushes the shared pool. The command respects the rule inherited from app_root_prof.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

--6.4 Execute the ALTER SYSTEM CHECKPOINT command that completes a checkpoint between the database buffer cache and the datafiles. The command respects the rule inherited from app_root_prof.
SQL> ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM CHECKPOINT
*
ERROR at line 1:
ORA-01031: insufficient privileges



--6.5 Execute the CREATE TABLE ... PARTITION command that creates a partitioned table requiring the partitioning option enabled. This command respects rules inherited from dynamic_op_prof.
SQL> CREATE TABLE sales
  2        ( SALESMAN_ID  NUMBER(5), SALESMAN_NAME VARCHAR2(30),
  3          SALES_STATE VARCHAR2(20))
  4         PARTITION BY LIST (SALES_STATE) AUTOMATIC
  5          (PARTITION P_CAL VALUES ('CALIFORNIA'));
CREATE TABLE sales
*
ERROR at line 1:
ORA-00439: feature not enabled: Partitioning

--7.Update the Base Lockdown Profile

--7.1 Log in to the HR_ROOT application root.
CONNECT hr_lock_mgr/sztech_4U@HR_ROOT

--7.2 Update the base lockdown profile
ALTER LOCKDOWN PROFILE        app_root_prof ENABLE STATEMENT = ('alter system')
               CLAUSE = ('checkpoint');

--7.3 Display the rules of the static and dynamic lockdown profiles. Read the list in code

col PROFILE_NAME for a20
col rule for a30
col CLAUSE for a20
SELECT profile_name, rule, clause, status FROM cdb_lockdown_profiles;

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

PROFILE_NAME         RULE                           CLAUSE               STATUS
-------------------- ------------------------------ -------------------- -------
APP_ROOT_PROF        ALTER SYSTEM                                        DISABLE
APP_ROOT_PROF        ALTER SYSTEM                   FLUSH SHARED_POOL    ENABLE
APP_ROOT_PROF        ALTER SYSTEM                   SET                  ENABLE
APP_ROOT_PROF        ALTER SYSTEM                   CHECKPOINT           ENABLE
DYNAMIC_OP_PROF      PARTITIONING                                        DISABLE
STATIC_SALES_PROF    ALTER PLUGGABLE DATABASE                            DISABLE
STATIC_SALES_PROF    ALTER SYSTEM                                        DISABLE
STATIC_SALES_PROF    ALTER SYSTEM                   FLUSH SHARED_POOL    ENABLE
STATIC_SALES_PROF    ALTER SYSTEM                   SET                  ENABLE

9 rows selected.

SQL>


--7.4 Log in to the SALES application PDB
conn hr_lock_mgr/sztech_4U@sales

--7.5 Execute the command allowed by the base lockdown profile. The static lockdown profile doesn't inherit the new rule of the base lockdown profile.

SQL> ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM CHECKPOINT
*
ERROR at line 1:
ORA-01031: insufficient privileges

--7.6 Log in to the OPERATIONS application PDB
conn hr_lock_mgr/sztech_4U@operations

--7.7 Execute the command allowed by the base lockdown profile. The dynamic lockdown profile inherits the new rule of the base lockdown profile.
SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL>


--8.Drop the Lockdown Profiles

--8.1 Connect to SALES as HR_LOCK_MGR
CONNECT hr_lock_mgr/sztech_4U@SALES

--8.2 Unset the static_sales_prof static lockdown profile in SALES
SQL> ALTER SYSTEM SET pdb_lockdown = '' SCOPE = both;

System altered.

--8.3 Connect to OPERATIONS as HR_LOCK_MGR
CONNECT hr_lock_mgr/sztech_4U@OPERATIONS

--8.4 Unset the dynamic_op_prof dynamic lockdown profile in OPERATIONS.
ALTER SYSTEM SET pdb_lockdown = '' SCOPE = both;

--8.5 Connect to HR_ROOT and drop both lockdown profile
CONNECT hr_lock_mgr/sztech_4U@HR_ROOT

SQL> DROP LOCKDOWN PROFILE static_sales_prof;

Lockdown Profile dropped.

SQL> DROP LOCKDOWN PROFILE dynamic_op_prof;

Lockdown Profile dropped.

--8.6 Unset the app_root_prof lockdown profile and then drop it
ALTER SYSTEM SET pdb_lockdown = '' SCOPE = both;

SQL> ALTER SYSTEM SET pdb_lockdown = '' SCOPE = both;

System altered.

SQL> DROP LOCKDOWN PROFILE app_root_prof;

Lockdown Profile dropped.

SQL>

郑全

以上功能在19.16 环境中测试通过。