Newbie schrieb am 24.05.2012 um 19:36 (+0300):

> I tried to grant execute permission for stored procedures for a role.
> but when this procedure is executed by a user in this role I get
> message, that rights doesn't exist on table.
>
> I see in IBExpert DDL that grant select on tables used by that
> procedure are already given to the stored procedure.

Could you provide the relevant part of SHOW GRANTS in isql?

> Question: is it sufficient that I grant a role to a proceudure? or
> should I grant that role also rights for each individual table as
> well?

In order for a procedure to execute successfully, it needs to have the
rights to do its jobs; either per grants to the procedure proper, or
per grants to the invoker of the procedure, which are then conferred
upon the procedure for when it is being executed.

> application does all access to data though stored procedures. so in
> reality users should not need to be granted rights on tables. how
> would be best practice to solve it?

Create a role APPL and GRANT it all required EXECUTE privileges. Then
grant APPL to PETER, PAUL and MARY.

As for the procedures, you could look at each one and grant it just the
privileges it needs. Very strict, very exact.

You could also make a list of all required privileges across all
procedures, package all those privileges in a role APPLSP, and then
grant that role to every single procedure. Less exact, but possibly
more convenient.

--
Michael Ludwig