Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You could create a view on that table, grant select on that view, and use that in your procedure? –  Munchi Nov 22 '13 at 19:32
add comment

2 Answers 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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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