有权限,但不想让其做的一个功能,只是这个功能只能在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>
|