I am new to Oracle (10g), my background is SQL Server. I have a service account that needs permission to execute a stored procedure, but nothing else.

I see from here how to add execute permissions for a single stored procedure: How do you grant execute permission for a single stored procedure?

Say I have a stored procedure GET_A(), which returns the results of a SELECT from Table A. I don't want to grant this user permission to query Table A directly. Will granting permission to execute GET_A() be sufficient?

I know in SQL Server I can grant a role access to specific Views and Stored Procedures, without granting permission to the underlying Tables. However, my in-house DBAs tell me this is not possible with Oracle.

  • You could create a view on that table, grant select on that view, and use that in your procedure?
    – Munchi
    Commented Nov 22, 2013 at 19:32

2 Answers 2


As long as you have your database organized in such a way that there is an account that is the owner of the tables and other objects, and your application connects using a separate account you can do what you want.

If you have tables and procedures organized in separate owners, you fist have to grant the required tables to the procedure owner (directly granted). Next you can grant the execute to a role or a user.

As soon as you allow connections to an object owner your security is broken since in that case you can always use all the objects of the owner (you are owner so you can use it).

  • This is the solution my DBAs implemented.
    – CigarDoug
    Commented Sep 2, 2014 at 10:14

This is indeed possible with stored procedures running with AUTHID DEFINER. Such SPs run with the authority of whoever created them. Please note that the creator must have explicit grants to the queried table; authorities acquired via roles will have no effect in a stored procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.