Tomas Krejzek wrote:

>
> We have two SQL users - admin and reader. I add one trigger - before update.
>
> This trigger check the change of two columns (cathegory, id_ex) from table and if they
> match the check it do same changes in new.xxx values and update 2 other tables. These two
> columns can be changed only by admin user, reader user can only read them.
>
> But when reader user try to change two other columns in the same table, it can't because
> he has no right to do changes that are in this new trigger.
> But he can never execute the body of the trigger, because of the check on start of the
> trigger.
>
> Is it necessary to check the dependences for all ways it can go in triggers and procedures
> before execution???

Yes it is, both from the standard definition of SQL and from the
architecture of triggers. Triggers aren't optional - they're integral
to the statement.

>
> Why ?? Is it so complicated to do it when the change/select/execution of procedure have to
> be done??

The problem is not with the implementation, it's with the concept.
Although you'd like to have triggers work conditionally depending
on the permissions of the user, most applications work better if
triggers are handled as part of the statement. What you may be
able to do - and I haven't tried - is to have the trigger invoke a
stored procedure using the values as input and have the procedure
test CURRENT_USER before changing the other tables. I don't recommend
that because it's one more thing you have to check when you're adding
users and changing permissions, but it might work.


Good luck,

Ann