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'。
我们来演示一下
正常情况下面,在VPD的hr用户下
这个时候,我们把这个表,赋予给其他的用户
可以看到test用户,即使有了select的权限,还是只能够看到我们限定的dept=100的内容。
即使我们赋予test dba的权限
下面我们再来看第二部分,表的在线重定义
可以看到vpd策略已经进行了copy
可以看到vpd策略已经进行了copy
--拷贝数据
可以看到,在完成整个在线重定义的过程中,trigger,以及约束始终是生效的,vpd在start的阶段就已经完成了copy。
所以,按照我们的实验,A,B,D都是错误的,与题库的答案有违背
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都是错误的,与题库的答案有违背