This content has been marked as final. Show 6 replies
Why would you want a situation where USER1 cannot see any of the data in the table but owns a procedure that allows him to update any row in the table? That would basically defeat the purpose of using VPD-- if USER1 can circumvent the VPD policy in this procedure, USER1 can circumvent the policy in any procedure and can create procedures that allow him to view and manipulate the data.
Can you provide a bit more background about what problem you're trying to solve? Why does USER1 need to own the procedure if USER1 isn't allowed to see any of the data? Are you trying to write a procedure that will apply the caller's VPD policy (i.e. when USER2 calls the procedure, he can only update the rows that his VPD policy allows him to see)? Or do you want the procedure code to bypass the VPD policy entirely? Why are you fine with granting USER2 the ability to bypass the VPD policy but you are not OK granting USER1 that same privilege?
since VPD is highly flexible it entirely depends on the design of the policy predicate function attached to the policy
what users are confronted with which restricted predicates to their end-user sql statements, this includes the table owner.
If you want the VPD policy to be applied in the scope of userB when calling the procedure, then you can
create it with caller rights by adding authid current_user, otherwise if procedures owned by the table
owner are considered safe, you can build an exception into the predicate function for the user USER1,
Harm ten Napel
USER1 can see the data. I should have not mentioned USER1 unable to access data in first post.
USER1 access is fine. Procedure and table are owned by USER1.
Now, USER2 having execute priv on the procedure and update priv on the table is able to run procedure but cannot update rows (0 rows updated). Now, when I grant "Exempt Access Policy" to USER1, USER2 can successfully update the rows. I do not want to grant exempt access policy to USER1. Is there a workaround solution that allows USER2 to run the procedure and update the table?