At 03:18 AM 14/12/2004 +0000, you wrote:
At 03:18 AM 14/12/2004 +0000, you wrote:

>I have a stored procedure which executes another stored procedure.

ProcA executes ProcB.

>The second stored procedure [ProcB] inserts a record into a particular
>table [TableA]. The user [userx] who is to execute the first stored
>procedure has execute permissions on it (via permissions on a role
>[rolex]), and the first stored procedure [ProcA] has execute permissions
>on the second SP [ProcB].

>Finally, the second SP [ProcB] has insert permissions on the table.

>Is this sufficient to allow this user to get data into this table via the
>first SP? If not, what am I missing? If it is sufficient, it isn't
>working. The error
>message I'm getting back from IBExpert tells me that the user does not
>have permission for read/select access to the table in question. If I
>grant the user's role select access on the table, then it complains
>about them not having insert access. If I grant insert access, it all
>works. But why should the user/role need insert access on the table if
>the SP has that access already and the user is granted permission to
>execute the SP? I'd rather the user not have any direct access to the
>table itself.

Have you possibly overlooked a foreign key relationship between TableA and
another table [TableB]? As a minimum, you would need to grant REFERENCES
privs to ProcB on TableB, the table referenced by a FK in TableA.

If what ProcB does causes triggers to fire that access other tables, then
ProcB might need privs to those affected tables, if access to them isn't
privileged higher up the chain.

Also, be sure that the user under which you are logged in actually has the
privileges to allow it to grant all of the privileges you are trying to
grant. For example, if you are not SYSDBA and you are not the owner of one
or other of the procedures, or the table, and you don't have WITH GRANT
OPTION privs for each of the privs you want to grant, then they won't take.

From what you described, this should work if you are sysdba or the owner
of everything:
CREATE ROLE ROLEX;
COMMIT;
GRANT ALL ON TABLEA TO PROCEDURE PROCB;
GRANT REFERENCES ON TABLEB TO PROCEDURE PROCB;
[GRANT ALL ON TABLEB TO PROCEDURE PROCB;]
[GRANT SOMEPRIVS ON TABLEX TO TRIGGER BLAH...;]
GRANT EXECUTE ON PROCEDURE PROCB TO PROCEDURE PROCA;
GRANT EXECUTE ON PROCEDURE PROCA TO ROLEX;
GRANT ROLEX TO USERA;

I hate these privileges questions 'coz it's impossible to give a "right"
answer that fits in with privs already in place in a particular
database. My advice is always to DESIGN your privilege schema (tree, map)
before you ever start granting privileges. For me, that means bringing my
Number One schema tool into play. (paper tablecloth + 3B pencil + big
eraser).

And test-Test-TEST (yep, I know you are...but evil spirits lurk in
RDB$USER_PRIVILEGES !!)

:-(
./heLen