Oracle VPD/RLS的谓词错误 [英] Predicate Error with Oracle VPD/RLS

问题描述

我需要有关Oracle的RLS功能的帮助.我以前从未使用过它，但是在网上进行了一些研究，但是我遇到了问题.

I need some help with Oracle's RLS feature. I have never used it before but did some research online about it, however i'm running into a problem.

这是我已采取的步骤:

1-创建函数:

create or replace function
   table_access_policy
   (obj_schema varchar2, obj_name varchar2) return varchar2

is                

v_project varchar2(2000);

begin              

v_project:= '(select project from users where user_name = (select sys_context(''userenv'',''session_user'') from dual))';

return 'project = ' || v_project;       

end;

2-添加安全策略

Begin

DBMS_RLS.ADD_POLICY ('db1', 'data', 'access_policy', 'admin', 'table_access_policy', 'SELECT' );

End;

3-在对象上运行简单的选择查询

3- Run a simple select query on the object

select * from db1.data

执行第3步时，出现以下错误:

When I do step 3 I get the following error:

ORA-28113:策略谓词有错误 28113. 00000-策略谓词有错误" *原因:策略函数生成无效的谓词. *操作:查看跟踪文件以获取详细的错误信息. 第5行错误:14列

ORA-28113: policy predicate has error 28113. 00000 - "policy predicate has error" *Cause: Policy function generates invalid predicate. *Action: Review the trace file for detailed error information. Error at Line: 5 Column: 14

我不知道安全功能有什么问题.我在db1模式上成功地手动使用了它，如下所示:

I don't know what could be wrong with the security function. I successfully used it manually on the db1 schema as follows:

select * from data
where project = (select project from users where user_name = (select sys_context('userenv','session_user') from dual))

有什么想法吗?

推荐答案

由于您的策略功能位于管理架构上，因此您应使用其架构对用户表中的选择进行限定:

Because your policy function is on the admin schema then you should qualify the select from the users table with its schema:

v_project:= '(select project from db1.users where user_name = (select sys_context(''userenv'',''session_user'') from dual))';

此外，您应该按照建议的方式检查跟踪文件，因为这将向您显示运行的确切SQL以及真实的错误消息.

In addition, you should check the trace file as it recommends as that will show you the exact SQL which is run as well as the real error message.

这篇关于Oracle VPD/RLS的谓词错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！