I have a table - TABLE1 and a PL/SQL procedure - 'PROC1' owned by USER1. PROC1 when executed, updates a row in TABLE1. PROC1 must be run by USERB and make an update. I have VPD - Row Level Security enabled on TABLE1.
Unless I grant "Exempt Access policy" to USER1 (Yes, USER1 is the table owner and still cannot bypass RLS to access data in its own table) I cannot have USERB successfully run the procedure and update the table. Grant this privilege to USERB has no use. I cannot grant it to USER1.
Any thoughts how I can overcome this? I still need USERB successfully execute the procedure.
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,
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?