I am trying to insert into an editioned view (view1 - defined on a table table1) in a procedure (defined in a package A in User U1) when called from another package B (defined in User U2). This errors out with insufficient privileges.
This is in Oracle 12c environment. The table1 and view1 is owned by user U1. There is an insert made into view1 through a procedure in Package A owned by user U1. Package B (in U2) calls this procedure to do the insert. 1) There is a synonym created in U2 for U1.view1 (checked in all_synonyms) 2) There is a Role (R1) which has the insert privilege to view1 (checked in ROLE_TAB_PRIVS) 3) Role R1 has been assigned to user U2 (checked in DBA_ROLE_PRIVS) 4) Role R1 is the default role 5) Package B is a definer rights and package A is an invoker rights 5) Everything is checked in the same edition
I expect the insert to happen into the view1 as the role is assigned to the user U2. But it does not happen, it errors out with "insufficient privileges". But when i grant an insert into this view1 to the user U2 explicitly then it works. Does this mean that access through roles only is not sufficient if the package A is having invoker rights?
"Does this mean that access through roles only is not sufficient if the package A is having invoker rights"
Simpler than that. We cannot build PL/SQL packages, views or triggers using privileges granted through roles. This is just the way the Oracle security model works. All privileges required to build such objects must be granted directly to the user.
Another gotcha is that if we build a view (or whatever) using privileges on an object in another schema and we want to grant access on our view to others then the privileges we receive must be granted WITH GRANT OPTION.