环境:oracle 11.2.0.4
问题:select distint 语句查询redact(加密)后的表报错不是selected表达式,而在加密之前执行同样的sql不报错,解决方案在文章最后面
实验:scott用户下创建view,加密策略才用只对hr用户加密。关于如何加密(redact)参考我另外一篇文章: http://blog.csdn.net/m0_37857602/article/details/77266295
下面开始加密准备工作:
REVOKE dba FROM SCOTT;
GRANT CONNECT, resource, unlimited tablespace TO SCOTT;
GRANT SELECT ON sys.redaction_policies TO SCOTT;
GRANT SELECT ON sys.redaction_columns TO SCOTT;
GRANT EXECUTE ON dbms_redact TO SCOTT;
create table students(name varchar2(10) primary key not null,age varchar2(10),count varchar2(10));
insert into students (name,age,count) values ('xxxxxx','vvvvvv','nnnnnn');
create table class(aa varchar2(10),name varchar2(10),cc varchar2(10));
insert into class (aa,name,cc) values ('zzzzzz','xxxxxx','cccccc');
创建视图view
create view view1 as select * from class;
select * from view1;
create view view2 as select * from students;
select * from view2;
赋予hr用户查询权限
grant select on scott.view1 to hr;grant select on scott.view2 to hr;
select * from scott.view1;
select * from scott.view2;
在加密之前先查询下面sql语句,是可以正常查询出结果,不报错
select distinct view1.name,view1.aa,view2.age
from view1,view2
where view2.name=view1.name
and view1.name<>'13'
and view1.cc='cccccc'
order by name ASC;
BEGIN
DBMS_REDACT.ADD_POLICY (
object_schema => 'SCOTT',
object_name => 'VIEW1',
policy_name => 'REDACT_EMP',
column_name => 'NAME',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVV,VVVVVV,*,3,6',
expression => '1=1',
enable => TRUE
);
END;
/
添加一列policy
BEGIN
DBMS_REDACT.ALTER_POLICY(
object_schema => 'SCOTT',
object_name => 'VIEW1',
policy_name => 'REDACT_EMP',
action => DBMS_REDACT.ADD_COLUMN,
column_name => 'AA',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => 'VVVVVV,VVVVVV,*,1,6'
);
END;
/
指定只对hr用户有效
BEGIN
DBMS_REDACT.ALTER_POLICY (
object_schema => 'SCOTT',
object_name => 'VIEW1',
policy_name => 'REDACT_EMP',
action => DBMS_REDACT.MODIFY_EXPRESSION,
expression => 'SYS_CONTEXT ( ''USERENV'',''SESSION_USER'' ) =''HR'''
);
END;
/
这时候再次测试下面sql:报错不是selected表达式
select distinct view1.name,view1.aa,view2.age
from view1,view2
where view2.name=view1.name
and view1.name<>'13'
and view1.cc='cccccc'
order by name ASC;
在hr用户下查询仍然相同的报错:
select distinct scott.view1.name,scott.view1.aa,scott.view2.age
from scott.view1,scott.view2
where scott.view2.name=scott.view1.name
and scott.view1.name<>'13'
and scott.view1.cc='cccccc'
order by name asc;
原因:按照语法规则,order by后面的谓词(name) 需要出现在distinct 后面的谓词中,而加密后编译器认为不一致了。需要改写上面的sql语句:
select name,aa,age
from (select distinct view1.name,view1.aa,view2.age
from view1,view2
where view2.name=view1.name
and view1.name<>'13'
and view1.cc='cccccc')
order by name asc;
感兴趣的朋友可以在上面环境测试以下sql:
select count(distinct name) from view1 order by name; -> 不报错
select distinct name from view1 order by name; ->报错 :不是selected表达式,加密后distinct与order by冲突。
大家可以在加密前后,分别在hr用户下执行下面sql,会发现执行结果不一致了,思考一下原因。
select count(distinct name) from scott.class;
删除策略方法:SCOTT用户下执行下面语句,这样就清空了redact加密环境
BEGINDBMS_REDACT.DROP_POLICY (
object_schema => 'SCOTT',
object_name => 'CLASS',
policy_name => 'REDACT_EMP');
END;
/