IT博客汇 | 【Q&A】12C OCP 1z0-060 QUESTION 2: Redefining a table with Virtual Private Database (VPD) policies

【Q&A】12C OCP 1z0-060 QUESTION 2: Redefining a table with Virtual Private Database (VPD) policies

dbstyle发表于 2016-03-15 00:57:06

QUESTION 2

Examine the following commands for redefining a table with Virtual Private Database (VPD) policies:

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'employees_policy',
function_schema => 'hr',
policy_function => 'auth_emp_dep_100',
statement_types => 'select,insert,update,delete',
);
END;

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'hr',
orig_table => 'employees',
int_table => 'employees_policy',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_PK,
orderby_cols => NULL,
part_name => NULL,
copy_vpd_opt => DBMS_REDEFINITION.CONS_VPD_AUTO);
END;

Which two statements are true about redefining the table?

All the triggers for the table are disabled without changing any of the column names or column types in the table.
The primary key constraint on the EMPLOYEES table is disabled during redefinition.
VPD policies are copied from the original table to the new table during online redefinition.
You must copy the VPD policies manually from the original table to the new table during online redefinition.

【题目示意】

考察了对配置了VPD策略的表进行在线重定义时的注意事项

【解析】

首先 VPD 是什么？VPD 全称 Virtual Private Database (VPD)，直译为虚拟私人数据库。启用VPD以后，可以在行级和列级编辑安全策略，用来对数据库进行访问控制，保护企业中名敏感的数据！其原理可以理解为，VPD 自动为 SQL 语句添加动态的 Where 条件，使得检索出来的数据符合定义的 VPD 策略。当用户直接或间接的访问被VPD策略所保护的某个表，视图或同义词时，Oracle数据库使用VPD策略中定义的函数的返回结果来修改where子句中的谓词条件，从而实现自动修改用户的SQL语句。

例如，用户执行了一个查询：

SELECT * FROM OE.ORDERS;

VPD策略动态的追加上Where 条件，

SELECT * FROM OE.ORDERS WHERE SALES_REP_ID = 159;

这样用户仅仅可以检索到 Sales Representative为159的数据行了。如果需要针对用户的session中的信息，如用户id，你可以使用上下文来实现！

SELECT * FROM OE.ORDERS WHERE SALES_REP_ID = SYS_CONTEXT(‘USERENV','SESSION_USER');

VPD 策略可以应用在Select，insert，update，delete，index语句中，但不能支持过滤DDL语句，如truncate table或alter table。

在线重定义可以改变表的结构，同时对重定义过程中表上的DML语句影响非常小。因为独占锁模式所经过的时间窗口比较短。主要操作通过DBMS_REDEFINITION包来完成。而在线重定义的表上同时有VPD策略，我们该如何处理呢？

首先 DBMS_REDEFINITION 包中提供了 copy_vpd_opt 参数，在 START_REDEF_TABLE 过程中使用 copy_vpd_opt 参数来处理VPD策略。copy_vpd_opt 参数可以指定如下一些内容：

DBMS_REDEFINITION.CONS_VPD_NONE 参数用于源表没有 VPD 策略时。

DBMS_REDEFINITION.CONS_VPD_AUTO 参数用于源表和中间表列名和类型相同时。要使用这个值，源表和中间表之间列的映射关系，也就是 col_mapping 参数，必须设置为 NULL 或者 ’*’ .还要注意，表的所有者和执行重定义的用户在重定义期间都可以访问中间表。

DBMS_REDEFINITION.CONS_VPD_MANUAL 参数，手工

1. 源表有 VPD 策略，同时源表和中间表有列的映射关系。

2. 在重定义中，需要修改和添加 VPD 策略

【实验】

1. 对hr用户下的employees表创建VPD策略所使用的函数，函数意味着除了hr用户意外，其他用户访问employees表时，只能访问部门编号为100的部门数据：

[oracle@dbstyle ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 5 12:19:31 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

SYS@DBSTYLE> conn hr/hr
Connected.
HR@DBSTYLE> desc employees;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

HR@DBSTYLE> CREATE OR REPLACE FUNCTION hr.auth_emp_dep_100(
2 schema_var IN VARCHAR2,
3 table_var IN VARCHAR2
4 )
5 RETURN VARCHAR2
6 AS
7 return_val VARCHAR2 (400);
8 unm VARCHAR2(30);
9 BEGIN
10 SELECT USER INTO unm FROM DUAL;
11 IF (unm = 'HR') THEN
12 return_val := NULL;
13 ELSE
14 return_val := 'DEPARTMENT_ID = 100';
15 END IF;
16 RETURN return_val;
17 END auth_emp_dep_100;
18 /

Function created.

2. 创建VPD策略：

HR@DBSTYLE> conn / as sysdba
Connected.
SYS@DBSTYLE> BEGIN
2 DBMS_RLS.ADD_POLICY (
3 object_schema => 'hr',
4 object_name => 'employees',
5 policy_name => 'employees_policy',
6 function_schema => 'hr',
7 policy_function => 'auth_emp_dep_100',
8 statement_types => 'select, insert, update, delete'
9 );
10 END;
11 /

PL/SQL procedure successfully completed.

3. 测试一下VPD策略的效果：

SYS@DBSTYLE> grant select on hr.employees to sh;

Grant succeeded.

SYS@DBSTYLE> conn sh/sh
Connected.
SH@DBSTYLE> select count(*) from hr.employees;

COUNT(*)
----------
6

SH@DBSTYLE> conn hr/hr
Connected.
HR@DBSTYLE> select count(*) from employees;

COUNT(*)
----------
107

HR@DBSTYLE> conn / as sysdba
Connected.

可以看到sh用户只返回了6行，而hr用户返回了107行。

4. 验证在线重定义过程是否可以使用主键来进行：

SYS@DBSTYLE> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','employees',
3 DBMS_REDEFINITION.CONS_USE_PK);
4 END;
5 /

PL/SQL procedure successfully completed.

5. 创建中间表：

SYS@DBSTYLE> CREATE TABLE hr.int_employees(
2 employee_id NUMBER(6),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 email VARCHAR2(25),
6 phone_number VARCHAR2(20),
7 hire_date DATE,
8 job_id VARCHAR2(10),
9 salary NUMBER(8,2),
10 commission_pct NUMBER(2,2),
11 manager_id NUMBER(6),
12 department_id NUMBER(4));

Table created.

6. 开始在线重定义过程：

SYS@DBSTYLE> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE (
3 uname => 'hr',
4 orig_table => 'employees',
5 int_table => 'int_employees',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_PK,
8 orderby_cols => NULL,
9 part_name => NULL,
copy_vpd_opt => DBMS_REDEFINITION.CONS_VPD_AUTO);
11 END;
12 /

PL/SQL procedure successfully completed.

7. 拷贝依赖的对象，会在中间表上自动创建触发器，索引，物化视图日志，权限，约束：

SYS@DBSTYLE> DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
5 uname => 'hr',
6 orig_table => 'employees',
7 int_table => 'int_employees',
8 copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
9 copy_triggers => TRUE,
10 copy_constraints => TRUE,
11 copy_privileges => TRUE,
12 ignore_errors => FALSE,
13 num_errors => num_errors);
14 END;
15 /

PL/SQL procedure successfully completed.

8. 检查表上的触发器：

SQL> select TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,STATUS from user_triggers;

TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS
-------------------- ---------------- -------------------- --------
SECURE_EMPLOYEES BEFORE STATEMENT EMPLOYEES DISABLED
UPDATE_JOB_HISTORY AFTER EACH ROW EMPLOYEES ENABLED
TMP$$_SECURE_EMPLOYE BEFORE STATEMENT INT_EMPLOYEES DISABLED
ES0

TMP$$_UPDATE_JOB_HIS AFTER EACH ROW INT_EMPLOYEES ENABLED
TORY0

9. 禁用中间表上的触发器，防止触发器在中间表上再次被触发：

SYS@DBSTYLE> ALTER TABLE hr.int_employees DISABLE ALL TRIGGERS;

Table altered.

10. 再次检查中间表上的触发器：

HR@DBSTYLE> select TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,STATUS from user_triggers;

TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS
-------------------- ---------------- -------------------- --------
SECURE_EMPLOYEES BEFORE STATEMENT EMPLOYEES DISABLED
UPDATE_JOB_HISTORY AFTER EACH ROW EMPLOYEES ENABLED
TMP$$_SECURE_EMPLOYE BEFORE STATEMENT INT_EMPLOYEES DISABLED
ES0

TMP$$_UPDATE_JOB_HIS AFTER EACH ROW INT_EMPLOYEES DISABLED
TORY0

11. 开始向中间表同步数据：

SYS@DBSTYLE> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
3 uname => 'hr',
4 orig_table => 'employees',
5 int_table => 'int_employees');
6 END;
7 /

PL/SQL procedure successfully completed.

12. 完成在线重定义：

SYS@DBSTYLE> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'hr',
4 orig_table => 'employees',
5 int_table => 'int_employees');
6 END;
7 /

PL/SQL procedure successfully completed.

13. 启用被禁用的触发器：

SYS@DBSTYLE> alter trigger hr.UPDATE_JOB_HISTORY enable;

Trigger altered.

【小结】

A. 源表的触发器复制到中间表后，在同步数据时，中间表的触发器会被触发，导致数据出现不一致，因此要关闭中间表的触发器，所以 A 正确。

B. 题目中的 ”options_flag => DBMS_REDEFINITION.CONS_USE_PK” 参数，说明使用主键来保证重定义过程中的唯一性，因此主键不会禁用，所以 B 错误。

C. 重定义中VPD策略的处理方法使用的是 DBMS_REDEFINITION.CONS_VPD_AUTO，含义为自动拷贝源表的VPD策略，所以 C 正确。

D. 如果使用 DBMS_REDEFINITION.CONS_VPD_MANUAL 参数，才需要手工拷贝源表的VPD策略，所以 D 不正确。

【答案】 A，C

http://docs.oracle.com/database/121/ADMIN/tables.htm#BABBIGBI

http://docs.oracle.com/database/121/DBSEG/vpd.htm#DBSEG007