ORACLE PL/SQL procedure permissions

Issue: Trying to issue an EXECUTE IMMEDIATE command from within a PL/SQL procedure and geting: ORA-01031: insufficient privileges while issuing an EXECUTE IMMEDIATE. The same EXECUTE IMMEDIATE command works from within an anonymous PL/SQL block. The procedure and the anonymous block both are run as the same user.

Resolution: Most likely, the user that you are running as has been granted the privilege(s) necessary through a role not via a direct grant. Definer’s rights stored procedures cannot utilize privileges granted to the owner via a role (roles can be enabled and disabled for a session, password protected, made default or non-default, etc. so it would be very difficult to figure out which set of roles, in general, the procedure should run with). Most likely, you just need to grant the owner of the procedure the privilege(s) needed directly.

You can confirm that this is the case by disabling roles for your session
SQL> set role none
and running the anonymous block. If that fails, the problem is that the user lacks the direct grant.
SQL> set role all

To see all privileges:

select * from USER_TAB_PRIVS
select * from USER_SYS_PRIVS
select * from USER_ROLE_PRIVS