Skip to Main Content
  • Questions

  • Insufficient privileges runinng procedure

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Boshoff.

Asked: August 04, 2021 - 6:16 am UTC

Last updated: August 05, 2021 - 3:28 am UTC

Version: 19c Autonomous

Viewed 1000+ times

You Asked

I am using Autonomous DB 19c in the Oracle Cloud.
When I run a procedure logged in as ADMIN user via sqldeveloper, do I run into insufficient privileges issues.
When I copy the code into a sqldeveloper window and run it not as a procedure ( also as ADMIN user ) does it work.
I have applied almost all privs under the sun to the ADMIN and procedure but it still does not work.
What am I missing ?

Output after trying to run PL/SQL procedure:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_AQADM", line 2093
ORA-06512: at "SYS.DBMS_AQADM", line 69
ORA-06512: at "SYS.DBMS_AQADM_INV", line 754
ORA-06512: at "SYS.DBMS_AQADM", line 65
ORA-06512: at "SYS.DBMS_AQADM", line 2072
ORA-06512: at "ADMIN.QUEUE_CLEANUP", line 21
ORA-06512: at line 2

and Chris said...

An example of the code you're running that errors would help a lot, but at a guess:

To do privileged actions when running a stored procedure, you need to grant these directly to the user. Not via a role.

Role access (only) works when running code in an anonymous block. See below for an example.

So I'm guessing there are privileges ADMIN has via a role that you need to execute your code. Find these and grant them to ADMIN.

grant dba to u identified by u;

conn u/u

create or replace procedure p as
begin
  execute immediate 'create table t ( x int )';
end p;
/

exec p();

ORA-01031: insufficient privileges

begin
  execute immediate 'create table t ( x int )';
end;
/

select count(*) from t;

COUNT(*)   
          0 

drop table t;

conn chris

grant create table to u;

conn u/u

exec p();

select count(*) from t;

COUNT(*)   
          0

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Missing grants for procedure

Boshoff Smit, August 05, 2021 - 1:16 am UTC

Hi.
Thank you for the feedback.
The PL/SQL code pointed to objects in another schema and called the SYS packages from within that schema also. I took the same procedure and ran it from within that schema and everything worked 100%..
Your feedback confirms that, and I will implement this workaround for now.
Definitely will not forget these direct grants and roles the next time I run into this issue with running procedures for another schema from a different schema.
Something so simple causing me a lot of grey hairs!
Connor McDonald
August 05, 2021 - 3:28 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library