Skip to Main Content
  • Questions
  • Privilege required to execute a stored procedure

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Durai.

Asked: January 16, 2017 - 12:52 pm UTC

Last updated: January 16, 2017 - 2:14 pm UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Hi,

There is a procedure named 'Update_emp_details' in our database which actually update the employees table present in the same database. I needs to give execute permission for a another user to this procedure 'Update_emp_details'.

Is giving execute privilege is enough for that user to run the procedure 'update_emp_details' or should i also needs to give modify privileges(insert,update,delete) for the table 'employees' ?

Please clarify me.

and Chris said...

As long as you created the procedure with definer's rights (the default), then you only need to grant execute on the procedure:

grant create session to u identified by u;

create table t (
  x int  
);

create or replace procedure p is
begin
  insert into t values (1);
end p;
/
grant execute on p to u;
grant select on t to u;

conn u/u
      
select count(*) from chris.t;

COUNT(*)  
0   

exec chris.p;

select count(*) from chris.t;

COUNT(*)  
1 


If you use invoker's rights, you need explicit privileges:

conn chris/chris

create or replace procedure p 
  authid current_user is
begin
  insert into t values (1);
end p;
/
grant execute on p to u;

conn u/u
      
select count(*) from chris.t;

COUNT(*)  
1  

exec chris.p;

ORA-00942: table or view does not exist

select count(*) from chris.t;

COUNT(*)  
1     


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

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