This content has been marked as final. Show 4 replies
i know there is an option to fetch all the rows from orders , without droping the policy , and of course from the same user.I'm not sure I understand. VPD would be pretty pointless if an unprivileged user could simply bypass the VPD policy whenever they liked.
can someone please tell me how ?
You could grant the OE user the EXEMPT ACCESS POLICY privilege. That would cause it to always bypass all VPD policies. That would generally defeat the purpose of having a VPD policy but it is useful for users like SYS.
You could also add something to the policy function that allows a user to toggle the policy by, say, setting a package variable. I'm hard-pressed to imagine why you would want to do that but you could.
Edited by: Justin Cave on Dec 20, 2012 1:21 PM
Applications contexts are special memory locations, in which you can store specific data (so for instance you don't have to query employee_id of currently connected user every time he is accessing some resource. Instead of that, you query it once and then store it in application context).
But that cannot override access policy. If you have policy created, then every time user accesses data, predicate will be added to query. The only way that you can avoid this is by granting user an EXEMPT ACCESS POLICY privilege.
An application context doesn't let you bypass a VPD policy.
If you choose to code the VPD policy to allow it, you could define a policy that returns '1=1' if a particular value is set in the context. For example
which assumes that you have create the context YOUR_CONTEXT and that you have populated the attribute SALES_REP_ID with the ID that you want to allow (or 'ALL' if you want to allow the user to access every row). Of course, you could base the logic in the VPD policy on anything you would like-- you could allow certain users or users with a particular group to get a predicate of '1 = 1' for example.
CREATE OR REPLACE FUNCTION auth_orders( schema_var IN VARCHAR2, table_var IN VARCHAR2 ) RETURN VARCHAR2 IS return_val VARCHAR2 (400); BEGIN IF( sys_context( 'YOUR_CONTEXT', 'SALES_REP_ID' ) = 'ALL' ) THEN return_val := '1 = 1'; ELSE return_val := 'SALES_REP_ID = to_number( SYS_CONTEXT(''YOUR_CONTEXT'', ''SALES_REP_ID'') )'; END IF; RETURN return_val; END auth_orders;
Edited by: Justin Cave on Dec 22, 2012 12:00 PM