RE: Security for Table/Procedure

[Mercadante, Thomas F]

Title: Security for Table/Procedure

Laura,

 

Have you looked at public synonyms for your stored procedures?  This will solve your problem of not being able to find the procedure.

 

Secondly, where are you creating the stored procedures?  I would create them in the same account as the tables, and grant execute access to whomever needs them.

 

Finally, you are correct in trying to resolve this now, before development gets too far along.  Write back to the list - we are all willing to help!

 

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 05, 2002 2:46 PM
To: Multiple recipients of list ORACLE-L
Subject: Security for Table/Procedure

I know that you can grant access on a table whether it be select, update, delete, etc, and I know that to keep from granting access to a table you can use procedures and grant execute rights to the procedure. 

Our situation is that we want to use procedures for security, but the procedures select data to populate a form and then update if applicable.  We had talked about granting select to the tables by using a role since anyone can view the data, and then using procedures to update the tables.  The only problem is that if we grant select to the tables and the application executes the procedure within it, the procedure will not be found and even the select will not happen.

Could some of you share how you handle security?  Am I missing something with tables/procedures?  Any ideas would be appreciated.  We are just beginning development and need to have a handle on how we are going to do this.

Thanks,

Laura