ettotev wrote:

> What I am asking for here is not actually the full row-level security
> mechanism. (btw, does anyone know if there are plans to introduce that
> in FB?) What I imagine I need would be statements like:
>
> GRANT SELECT ON TRAN TO PUBLIC;
> GRANT ALL ON TRAN WHERE TRATYPE = 'N' TO PUBLIC;
> GRANT ALL ON TRAN WHERE TRATYPE = 'X' TO TRIGGER XGHG_AUID0;
>

I don't know what the SQL standard says about row level permissions, but
I think it 's very usefull in a lot of situations.

<snip>

> A major design requirement is that the database should be able to
> enforce all business rules by itself, so that a user cannot create
> invalid data even when they connect via a generic administration
> client like isql or flamerobin or ibexpert. This means that all
> solutions on the "application" side are not acceptable.
>

I would just permit modifications on the restricted table trough SP's
and implement the security on that SP. But it has drawbacks as well.

<snip>

> The direction I'm currently thinking in is to create a BEFORE trigger
> for TRAN, where all operations for TRATYPE<>'N' are rejected if they
> are not coming from the respective trigger on the other table (in the
> example above, trigger XCHG_AUID0 for TRATYPE='X'). Then the question
> is - how can I know when a request is coming from that specific
> trigger and when not?
>

you could do something like this:
Create a table (let's call it "permission table") that could be only
inserted by that trigger, in the beginning of the trigger insert a row
indicating that the desired record could be modified. in the before
update trigger you mentioned above you will check if TRATYPE = 'N' or
the record key is on the "permission table" if so, it's allowed to be
modified, after the update is done (on the allowed trigger) delete the
record fro the "permission table"

> Thanks in advance
> Emil
>

HTH

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br