Home » RDBMS Server » Server Administration » DBMS_RLS.ADD_POLICY (12.1.0.2.0 )
DBMS_RLS.ADD_POLICY [message #668525] Wed, 28 February 2018 01:28 Go to next message
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';
Re: DBMS_RLS.ADD_POLICY [message #668529 is a reply to message #668525] Wed, 28 February 2018 04:48 Go to previous messageGo to next message
John Watson
Messages: 7672
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?
Re: DBMS_RLS.ADD_POLICY [message #668549 is a reply to message #668529] Thu, 01 March 2018 01:04 Go to previous messageGo to next message
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
Re: DBMS_RLS.ADD_POLICY [message #668550 is a reply to message #668549] Thu, 01 March 2018 01:14 Go to previous messageGo to next message
John Watson
Messages: 7672
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?
Re: DBMS_RLS.ADD_POLICY [message #668554 is a reply to message #668550] Thu, 01 March 2018 05:09 Go to previous messageGo to next message
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')
Re: DBMS_RLS.ADD_POLICY [message #668555 is a reply to message #668554] Thu, 01 March 2018 05:12 Go to previous messageGo to next message
John Watson
Messages: 7672
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?
Re: DBMS_RLS.ADD_POLICY [message #668556 is a reply to message #668555] Thu, 01 March 2018 05:18 Go to previous messageGo to next message
shyam ragunathan
Messages: 6
Registered: February 2018
Junior Member
my predicate is not getting applied it has to come like

access("EMP_NO"='000002' and "EMP_NO"='000003'), why it is not
Re: DBMS_RLS.ADD_POLICY [message #668557 is a reply to message #668556] Thu, 01 March 2018 05:19 Go to previous messageGo to next message
shyam ragunathan
Messages: 6
Registered: February 2018
Junior Member
can you come in private messaging
Re: DBMS_RLS.ADD_POLICY [message #668558 is a reply to message #668557] Thu, 01 March 2018 05:20 Go to previous messageGo to next message
John Watson
Messages: 7672
Registered: January 2010
Location: Global Village
Senior Member
shyam ragunathan wrote on Thu, 01 March 2018 11:19
can you come in private messaging
Are you asking for consultancy services?
Re: DBMS_RLS.ADD_POLICY [message #668559 is a reply to message #668558] Thu, 01 March 2018 05:24 Go to previous message
shyam ragunathan
Messages: 6
Registered: February 2018
Junior Member
Are you asking for consultancy services?
no. Fine. Any idea on my issue
Previous Topic: Changing Noninterval to Interval Partitions
Next Topic: Reclaiming Space From Tables
Goto Forum:
  


Current Time: Tue Nov 20 22:12:26 CST 2018