Grant permission to execute stored procedure without granting access to underlying table - Oracle

Question

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.

Answer 1

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).

Answer 2

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.