Re: Help with Stored Procedure [Basic]

Amy Leone (amyleone_at_nyc.pipeline.com) wrote:
: >Yes, this all works very well, but what does not seem to work is when
: >the insert/update/select/delete has been granted to the user (who
: >the procedure is compiled under) via a role. The same permissions
: >are there but they are not enabled when running the stored procedure.
: >
: >Tony
:
: Well, I imagine that has something to do with the fact that roles are to be
: granted to users, not developers. It's tied in with the whole philosophy
: behind using roles.
:
: Amy

Here's our particuliar scenario:

"data1" owns the tables with the desired data.

"proc1" owns the procedures that users have access to.

data1 creates a role "reader" and grants select to all the tables desired to the role "reader". This should give any user with the "reader" role enabled select priviledge on the table(s).

Proc1 has the "reader" role enabled and creates a stored procedure that selects from data1 tables.

User1 executes proc1.read_procedure and fails because the priviledges granted via the role to proc1 are not recognized. The only way for this to work is to explicitly grant the privileges, that is, data1 must grant select to proc1. Then it works.

I think I understand why it works this way, but was hoping to get a clearer understanding.

Tony Received on Fri Mar 08 1996 - 00:00:00 CET