OraFAQ Forum: Server Administration » DBMS_RLS.ADD

Home » RDBMS Server » Server Administration » DBMS_RLS.ADD_POLICY (12.1.0.2.0 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_RLS.ADD_POLICY [message #668525]

Wed, 28 February 2018 01:28

shyam ragunathan
Messages: 6
Registered: February 2018

Junior Member

DBMS_RLS.ADD_POLICY

Policy is getting created but still the user able to see the all rows

DBMS_RLS.ADD_POLICY(object_schema=> 'hr',
object_name=>'EMPLOYE',
policy_name=>'EMPLOYE'||'RLS_POLICY',
function_schema=>'hr',
policy_function=>'fnc',
update_check=>true);

Policy available in
SELECT * FROM DBA_POLICIES WHERE object_owner='UBS1202WPS';

Report message to a moderator

Re: DBMS_RLS.ADD_POLICY [message #668529 is a reply to message #668525]

Wed, 28 February 2018 04:48

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

You need to provide more information:
As which user are you running the query? Does he have the EXEMPT ACCESS POLICY privilege?
Does the table hr.EMPLOYE actually exist? Does the function hr.fnc exist, and how did you create it?
If you enable autotrace for the query, do you see the predicate being applied?

Report message to a moderator

Re: DBMS_RLS.ADD_POLICY [message #668549 is a reply to message #668529]

Thu, 01 March 2018 01:04

shyam ragunathan
Messages: 6
Registered: February 2018

Junior Member

As which user are you running the query? am using hr user only

Does he have the EXEMPT ACCESS POLICY privilege? what is this?

Does the table hr.EMPLOYE actually exist?yes is is avilable

Does the function hr.fnc exist, and how did you create it?
yes it is available

CREATE OR REPLACE FUNCTION fnc (p_Schema VARCHAR2, p_Table VARCHAR2) RETURN VARCHAR2
AS
BEGIN
RETURN 'EMP_NO = ''000003''';
END fnc;

If you enable autotrace for the query, do you see the predicate being applied?
i used
SELECT * FROM DBA_POLICIES WHERE object_owner='hr' and i can see the policy is getting applied.

i have a different db of 12Cr2 in it it is working fine.facing issue in 12Cr1 DB only

Report message to a moderator

Re: DBMS_RLS.ADD_POLICY [message #668550 is a reply to message #668549]

Thu, 01 March 2018 01:14

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

1. Please use [code] tags in your posts.

2. Does he have the EXEMPT ACCESS POLICY privilege? what is this?
It is described in the docs.

3. If you enable autotrace for the query, do you see the predicate being applied?

Report message to a moderator

Re: DBMS_RLS.ADD_POLICY [message #668554 is a reply to message #668550]

Thu, 01 March 2018 05:09

shyam ragunathan
Messages: 6
Registered: February 2018

Junior Member

1. Please use [code] tags in your posts.
code tags ?? i am not getting it

2. Does he have the EXEMPT ACCESS POLICY privilege? yes the user have EXEMPT ACCESS POLICY

3. If you enable autotrace for the query, do you see the predicate being applied?

select * from EMPLOYE where EMP_NO = '000002';
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMP_NO"='000002')
filter("EMP_NO"='000002')

Report message to a moderator

Re: DBMS_RLS.ADD_POLICY [message #668555 is a reply to message #668554]

Thu, 01 March 2018 05:12

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Quote:

code tags ?? i am not getting it

Quote:

yes the user have EXEMPT ACCESS POLICY

You haven't read anything I've suggested, have you?
Quote:

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMP_NO"='000002')
filter("EMP_NO"='000002')

Well? Do you see your predicate being applied?

Report message to a moderator