oracle redact数据编译后 查询报错 不是selected表达式

环境：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;

在数据库自带的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加密环境

BEGIN
  DBMS_REDACT.DROP_POLICY (
    object_schema  => 'SCOTT',
    object_name    => 'CLASS',
    policy_name    => 'REDACT_EMP');
END;
/