12C-OCP升级1z-060-002

Examine the following commands for redefining a table with Virtual Private Database (VPD)
policies:
Which two statements are true about redefining the table?
A. All the triggers for the table are disabled without changing any of the column names or
column types in the table.
B. The primary key constraint on the EMPLOYEES table is disabled during redefinition.
C. VPD policies are copied from the original table to the new table during online
redefinition.
D. You must copy the VPD policies manually from the original table to the new table during
online redefinition.
Answer: A,C
Explanation: The triggers cloned to the interim table are disabled until the redefinition is
completed. Once the redefinition is complete, all cloned objects are renamed to the original
names used by they objects they were cloned from.

这道题目主要考察了两个部分，第一个是VPD，第二个是在线重定义，我们分别来看下这两个部分的内容
首先我们来看一下Virtual Private Database (VPD)
从Oracle产品属性来看，Oracle Virtual Private Database（简称VPD）是归属在Oracle安全security框架下的成熟产品。要注意：VPD是企业版Enterprise版本功能，在其他如标准Standard版下是不能使用的。
简单的说，VPD就是介于用户SQL语句和实际执行对象之间的介质层。用户或者应用程序发出的SQL语句在传入到DBMS执行之前，会自动被拦截并且进行额外处理。处理的结果往往反映为在语句where条件中添加特殊的条件式。
例如：数据表T中包括了所有员工的通信信息，当每个员工登陆HR系统时，能查询到所有的员工通讯信息。但是现在业务需求变了，当员工登陆HR系统后，应该只能查到自己的信息。在没有VPD的情况下，我们必须修改应用程序代码，将username=’自己的名字’加入到所有对应数据表操作SQL语句中。业务逻辑应该如下：
限制用户只能从T表中查询到和自己用户名匹配的记录，例如：A用户登陆后，执行select * from T，在VPD的作用下，相当于为这条语句增加了一个隐含的条件where username='A'，等于执行了select * from T where username='A'仅返回符合username='A'这一条件的记录.
借助VPD，应用程序不需要修改任何代码。我们只需要在数据库层面设定一个指定策略规则，如果针对某个数据对象表的所有SQL语句，都会调用一个设定的函数。函数自身会返回一个字符串条件，作为补充的where语句条件。
例如：如果我们设定对数据表t的每个Select语句都要添加一个条件username='a'，那么即使应用层发出了select * from t，那么实际执行的也都是select * from t where userame='a'。
我们来演示一下

grant execute on dbms_rls to hr;
grant select any dictionary to hr;

创建一个过滤的函数

create or replace function auth_emp_dept_100
 (d1 varchar2,
  d2 varchar2
 ) return varchar2
is
 begin
 if sys_context('USERENV','SESSION_USER') IN ('SYS','SYSTEM') then
 return null;
 else
  return 'dept=100';
end if;
end;
/

添加激活一个策略

exec dbms_rls.add_policy(object_schema => 'hr',object_name => 'employees',policy_name => 'employees_policy',function_schema => 'hr',policy_function => 'auth_emp_dept_100',statement_types => 'select,insert,update,delete');

上面两步操作的意义是，在用户非SYS或者是SYSTEM的情况下面，除了VPD的特权用户HR，其他的用户，只能够看到dept=100的数据
正常情况下面，在VPD的hr用户下

05:09:37 hr@prod1> select distinct dept from employees;
         DEPT
-------------
            1
           51
          100
           25
           13
            2
           21
           14
            4
            5
           24
           32
            8
           38
           23
           93
           52
           64
            3
           36
            9
21 rows selected.

我们可以看到全部的内容
这个时候，我们把这个表，赋予给其他的用户

grant select on employees to test;
05:14:43 test@prod1> select distinct dept from hr.employees;
         DEPT
-------------
          100

可以看到test用户，即使有了select的权限，还是只能够看到我们限定的dept=100的内容。
即使我们赋予test dba的权限

05:21:08 sys@prodcdb> grant dba to test;
Grant succeeded.
05:21:54 test@prod1> select distinct dept from hr.employees;
         DEPT
-------------
          100

而我们通过sys或者system用户连接的时候，是能够看到全部的数据的

05:33:00 system@prod1> select distinct dept from hr.employees;
         DEPT
-------------
            1
           51
          100
           25
           13
            2
           21
           14
            4
            5
           24
           32
            8
           38
           23
           93
           52
           64
            3
           36
            9
21 rows selected.

通过这样的方式，就实现了用户与数据的隔离，提高了安全性
下面我们再来看第二部分，表的在线重定义

col object_name for a30
col policy_name for a30
select object_name,POLICY_NAME,SEL, INS, UPD, DEL, IDX, CHK_OPTION, ENABLE from user_policies;
OBJECT_NAME                    POLICY_NAME                    SEL INS UPD DEL IDX CHK ENA
------------------------------ ------------------------------ --- --- --- --- --- --- ---
EMPLOYEES                      EMPLOYEES_POLICY               YES YES YES YES NO  NO  YES

我们在表上面创建一个DML触发器

CREATE TABLE logs(  
        LOG_ID NUMBER(10) PRIMARY KEY,  
        LOG_TABLE VARCHAR2(10) NOT NULL,  
        LOG_DML VARCHAR2(10),  
        LOG_KEY_ID NUMBER(10),  
        LOG_DATE DATE,  
        LOG_USER VARCHAR2(15)  
        ); 

CREATE SEQUENCE logs_id_squ INCREMENT BY 1   
        START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;  

CREATE OR REPLACE TRIGGER DML_LOG  
        BEFORE --触发时间为操作前  
        DELETE OR INSERT OR UPDATE -- 由三种事件触发  
        ON employees  
        FOR EACH ROW -- 行级触发器  
        BEGIN  
IF INSERTING THEN   
        INSERT INTO logs        VALUES(logs_id_squ.NEXTVAL,'EMPLOYEES','INSERT',:new.object_id,SYSDATE,USER);  
         ELSIF DELETING THEN  
         INSERT INTO logs       VALUES(logs_id_squ.NEXTVAL,'EMPLOYEES','DELETE',:old.object_id,SYSDATE,USER);  
         ELSE  
             INSERT INTO logs       VALUES(logs_id_squ.NEXTVAL,'EMPLOYEES','UPDATE',:new.object_id,SYSDATE,USER);  
        END IF;  
        END;  
        /

col TRIGGER_NAME for a30
col TRIGGER_NAME for a20
col TRIGGER_TYPE for a20
col TABLE_OWNER for a20
col TABLE_NAME for a20
select TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,status from user_triggers;
TRIGGER_NAME         TRIGGER_TYPE         TABLE_OWNER          TABLE_NAME           STATUS
-------------------- -------------------- -------------------- -------------------- --------
DML_LOG              BEFORE EACH ROW      HR                   EMPLOYEES            ENABLED

alter table employees add constraint pk_employees primary key(object_id);
创建中间表
05:59:58 hr@prod1> create table int_employees as select * from employees where 1=0;
Table created.
exec dbms_redefinition.START_REDEF_TABLE(UNAME=>'hr',ORIG_TABLE=>'employees',INT_TABLE=>'int_employees',COL_MAPPING=>null,OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_PK,ORDERBY_COLS=>null,PART_NAME=>null,COPY_VPD_OPT=>DBMS_REDEFINITION.CONS_VPD_AUTO);
06:12:07 hr@prod1> select TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,status from user_triggers;
TRIGGER_NAME         TRIGGER_TYPE         TABLE_OWNER          TABLE_NAME           STATUS
-------------------- -------------------- -------------------- -------------------- --------
DML_LOG              BEFORE EACH ROW      HR                   EMPLOYEES            ENABLED

可以看到vpd策略已经进行了copy

06:13:51 hr@prod1> select object_name,POLICY_NAME,SEL, INS, UPD, DEL, IDX, CHK_OPTION, ENABLE from user_policies;
OBJECT_NAME                    POLICY_NAME                    SEL INS UPD DEL IDX CHK ENA
------------------------------ ------------------------------ --- --- --- --- --- --- ---
INT_EMPLOYEES                  RD_PLCY$$INT_EMPLOYEE          YES YES NO  YES NO  YES YES
EMPLOYEES                      EMPLOYEES_POLICY               YES YES YES YES NO  NO  YES

可以看到vpd策略已经进行了copy

select CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,STATUS from  user_constraints;
CONSTRAINT_NAME                TABLE_NAME           C STATUS
------------------------------ -------------------- - --------
SYS_C009990                    LOGS                 C ENABLED
PK_EMPLOYEES                   EMPLOYEES            P ENABLED
SYS_C009991                    LOGS                 P ENABLED

--拷贝数据

declare
num_errors PLS_INTEGER;
begin  
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('HR','EMPLOYEES','INT_EMPLOYEES',dbms_redefinition.cons_orig_params,true,true,true,true,num_errors);
end;
/
06:27:47 hr@prod1> select CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,STATUS from  user_constraints;

约束进行了copy
CONSTRAINT_NAME                TABLE_NAME           C STATUS
------------------------------ -------------------- - --------
SYS_C009990                    LOGS                 C ENABLED
TMP$$_PK_EMPLOYEES0            INT_EMPLOYEES        P ENABLED
PK_EMPLOYEES                   EMPLOYEES            P ENABLED
SYS_C009991                    LOGS                 P ENABLED

Elapsed: 00:00:00.72
06:28:03 hr@prod1> select TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,status from user_triggers;

Trigger进行了copy
TRIGGER_NAME         TRIGGER_TYPE         TABLE_OWNER          TABLE_NAME           STATUS
-------------------- -------------------- -------------------- -------------------- --------
DML_LOG              BEFORE EACH ROW      HR                   EMPLOYEES            ENABLED
TMP$$_DML_LOG0       BEFORE EACH ROW      HR                   INT_EMPLOYEES        ENABLED

--完成重定义

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE ('HR','EMPLOYEES','INT_EMPLOYEES');
06:33:06 hr@prod1> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE ('HR','EMPLOYEES','INT_EMPLOYEES');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.58
06:33:25 hr@prod1> select object_name,POLICY_NAME,SEL, INS, UPD, DEL, IDX, CHK_OPTION, ENABLE from user_policies;
OBJECT_NAME                    POLICY_NAME                    SEL INS UPD DEL IDX CHK ENA
------------------------------ ------------------------------ --- --- --- --- --- --- ---
EMPLOYEES                      EMPLOYEES_POLICY               YES YES YES YES NO  NO  YES
INT_EMPLOYEES                  EMPLOYEES_POLICY               YES YES YES YES NO  NO  YES
Elapsed: 00:00:00.16
06:33:37 hr@prod1> select TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,status from user_triggers;
TRIGGER_NAME         TRIGGER_TYPE         TABLE_OWNER          TABLE_NAME           STATUS
-------------------- -------------------- -------------------- -------------------- --------
TMP$$_DML_LOG0       BEFORE EACH ROW      HR                   INT_EMPLOYEES        ENABLED
DML_LOG              BEFORE EACH ROW      HR                   EMPLOYEES            ENABLED
Elapsed: 00:00:00.14
06:33:46 hr@prod1> select CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,STATUS from  user_constraints;
CONSTRAINT_NAME                TABLE_NAME           C STATUS
------------------------------ -------------------- - --------
SYS_C009990                    LOGS                 C ENABLED
PK_EMPLOYEES                   EMPLOYEES            P ENABLED
TMP$$_PK_EMPLOYEES0            INT_EMPLOYEES        P ENABLED
SYS_C009991                    LOGS                 P ENABLED

可以看到，在完成整个在线重定义的过程中，trigger，以及约束始终是生效的，vpd在start的阶段就已经完成了copy。
所以，按照我们的实验，A，B，D都是错误的，与题库的答案有违背