VPD policy to form 2 where clauses

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

VPD policy to form 2 where clauses

Postby me_lucky » Mon Sep 16, 2013 7:02 am

Hello Tim,

I have requirement where i need to define a VPD policy in such a way that it should for two where clauses dynamically.

eg:

select * from employee
where
empname = ??
or mgrname = ??

I have tried to define two policies and executed , but it is forming AND instead of OR .

I have searched over google but dint find any information.
I want to know if this is possible with VPD or not.

Thanks in Advance,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Mon Sep 16, 2013 7:15 am

Hi.

Are you building the whole where clause, or just the last predicate?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Mon Sep 16, 2013 7:42 am

Hello Tim,

I just want to pass the predicate value not the complete where clause.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby me_lucky » Mon Sep 16, 2013 8:06 am

sorry Tim,

May be i have not understood your question properly.

Will explain clearly..

I have a table test, and column a, b, c, d

I have defined two policies on table test, column a, b individually.

when i execute the below command:

1) Set the context ( here i will set two context for two policies defined on that table)
2) select * from test

O/P: my query will be formed as below with VPD:

select * from test
where a=1
and b=1

But i want to get the query formed as:

select * from test
where a=1
OR b=1

I hope now you have got my point.

Sorry if am confusing. Pl let me know if u need any information.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Mon Sep 16, 2013 10:26 am

Hi.

So why two policies? Why not 1 policy, with two contexts. The policy can build the predicates according to the context values, thereby allowing you to build the predicate with an OR.

Code: Select all
IF val1 IS NOT NULL and VAL2 IS NOT NULL THEN
  -- Both columns using OR
IF VAL1 IS NOT NULL AND val2 IS NULL THEN
  -- just 1st col.
IF VAL1 IS NULL AND val2 IS NOT NULL THEN
  -- just 2nd col.
ELSE
  NULL;
END IF;


See what I mean?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Mon Sep 16, 2013 10:45 am

Hi Tim,

Thanks for the update. i understood your suggestion.

I don't want to use those columns as part of my query, those 2 columns should be added dynamically to the where clauses.

Hope you got me.

Thanks & Regards,
Lucky.
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Mon Sep 16, 2013 6:43 pm

Hi.

I am talking about them being added dynamically in your code that is called by the policy.

The point is, one function call from one policy can build up a series of predicates in one shot. it doesn't have to be one policy per predicate.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Wed Sep 18, 2013 7:02 am

Hello Tim,

Thanks for your update. Yes i understood you point.
It is not in function/procedure. It is static code hard coded .

We you said we can achieve this when it is called from function. Is the same possible for static queries?

If it can achieved pl let me know other wise i'l try if i can put it in function.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Wed Sep 18, 2013 9:25 am

Hi.

You have misunderstood what I said completely.

This is how you use VPD:

http://www.oracle-base.com/articles/8i/ ... abases.php

Your security policy calls some PL/SQL to determine what predicates needs to be generated. Put your logic for generating multiple predicates into that code, using the OR and it is fine.

I am not suggesting you write all your SQL in functions. I'm suggesting you use VPD the way it is intended to be used. In your policy function, you can do whatever you want. It is up to you to build the predicates that get added by the policy.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Thu Sep 19, 2013 5:43 am

Hello Tim,

I am extremely sorry for my blunder mistake. Yes i completely misunderstood you now.

Now i understood you point clearly. It is very simple.
Actually i seems to very difficult for me, so was thing in very wrong way, but you made it very simple.

Ahhh thanks a ton to you.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Thu Sep 19, 2013 6:50 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Thu Sep 19, 2013 7:10 am

Hello Tim,

Again need your help.

I have written my function as below:

FUNCTION F_CONTCHAS_POLICY (p_schema VARCHAR2, p_obj VARCHAR2)
RETURN VARCHAR2
IS
d_predicate varchar2(2000) DEFAULT NULL;
BEGIN
IF SYS_CONTEXT('CONT_CONTEXT', 'CONT_NU') IS NOT NULL
AND SYS_CONTEXT('CHAS_CONTEXT', 'CHAS_NU') IS NOT NULL
THEN
d_predicate := 'CONT_NU='||'sys_context(''CONT_CONTEXT'',''CONT_NU'') OR CHAS_NU='||'sys_context(''CHAS_CONTEXT'',''CHAS_NU'')';
END IF;
RETURN d_predicate;
END;

and when i execute my query, its giving wrong result (Its giving AND result instead of 'OR') Pl see the out from trace:

SELECT COUNT(*) "COUNT(*)"
FROM (SELECT "indexjoin$_alias$_002"."CONT_NU" "CONT_NU",
"indexjoin$_alias$_001"."CHAS_NU" "CONTRACT_ID",
"indexjoin$_alias$_001".ROWID "ROWID"
FROM "CONT_CHAS_TAB" "indexjoin$_alias$_001",
"CONT_CHAS_TAB" "indexjoin$_alias$_002"
WHERE "indexjoin$_alias$_001"."CHAS_NU" =
SYS_CONTEXT('CHAS_CONTEXT',
'CHAS_NU')
AND "indexjoin$_alias$_002".ROWID = "indexjoin$_alias$_001".ROWID
AND "indexjoin$_alias$_002"."CONT_NU" =
SYS_CONTEXT('CONT_CONTEXT',
'CONT_NU')) "CONT_CHAS_TAB"
WHERE "CONT_CHAS_TAB"."CHAS_NU" =
SYS_CONTEXT('CHAS_CONTEXT', 'CHAS_NU')
OR "CONT_CHAS_TAB"."CONT_NU" =
SYS_CONTEXT('CONT_CONTEXT',
'CONT_NU')

Where am wrong , pl advice me.

Thanks & Regards,
Lucky.
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Thu Sep 19, 2013 10:01 am

Hi.

Your problem is how you are using OR.

Think about this:

Code: Select all
where a = 1
and b = 2
and c = 3 or d = 4


That actually means:

Code: Select all
where (a = 1 and b = 2 and c = 3)
or d = 4


When what you probably think it means is:

Code: Select all
where a = 1
and b = 2
and (c = 3 or d = 4)


Be very careful when using OR that you restrict its scope with braces.

So I think you need:

d_predicate := '(CONT_NU=sys_context(''CONT_CONTEXT'',''CONT_NU'') OR CHAS_NU=sys_context(''CHAS_CONTEXT'',''CHAS_NU''))';

Note. I'm not sure why you were concatenating the strings together. It can be written as one continuous like as I have done.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Thu Sep 19, 2013 11:02 am

Hello Tim,

I did the changes as said but still the same behavior.

Thanks & Regards,
Lucky
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Thu Sep 19, 2013 11:03 am

Hi.

Can you post your changed predicate please?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17951
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Next

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest