--- In firebird-support@yahoogroups.com, Ales Smodis <aless@z...> wrote:

> Hello!
>
> I'll be short and practical. :)
> Consider the following:
>
> root:/tmp$ isql -user SYSDBA -pass masterkey
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> create database "test.fdb";
> SQL> create table stuff (place integer not null primary key, thing
> varchar(20));
> SQL> set term !! ;
> SQL> create procedure something (how smallint, place integer, thing
> varchar(20), was Integer) as
> CON> begin
> CON> if (how = 1) then
> CON> insert into stuff (place, thing) values (:place, :thing);
> CON> else if (how = 2) then
> CON> update stuff set place = :place, thing = :thing where place

= :was;

> CON> else if (how = 3) then
> CON> delete from stuff where place = :place;
> CON> end !!
> SQL> set term ; !!
> SQL> grant select,insert on stuff to aless;
> SQL> grant execute on procedure something to aless;
> SQL> quit;
>
> And now what's boggling my mind:
>
> root:/tmp$ isql -user ALESS -pass whatever test.fdb
> Database: test.fdb, User: ALESS
> SQL> insert into stuff (place, thing) values (1, 'something');
> SQL> execute procedure something (1, 2, 'next thing', 0);
> Statement failed, SQLCODE = -551
>
> no permission for update/write access to TABLE STUFF
> SQL> quit;
>
> Why is it required in the example above that I should also have update
> and delete privileges on the table if all the procedure should do is
> just insertion, although it is capable of doing other things, too?

a) Procedures are compiled, not interpreted line-by-line.
b) Rights are checked on prepare, not on execute.

So, even if parameters for particular call will not cause execution
of all statements within procedure, rights will be checked for all
objects which procedure CAN affect. More, if on this objects are
defined triggers which CAN affect another objects, rights for this
actions will be checked too. Perhaps it will be more convenient to you
to give all necessary for procedure rights to procedure itself and
give user only right to execute procedure, doing so you don't give to
user rights to modify tables directly. Note you can give rights to
triggers too. And last, note that if you give users/objects
modify/delete rights only, they can't perform modifying statements
with WHERE clause on this table, only modify/delete all rows in a
table. This I treat as bug and registered in FB tracker.

Best regards,
Alexander.