setuser in procedures

[Baron]

How can I change the user within a procedure?

I have a trigger t1 on a table T1, and I want to deactivate this trigger for all the transactions coming from a specific procedure1:

create "proc_user"."procedure1"()
begin
   insert into T1 values (value1, value2)
end;

create trigger t1 after insert on T1
....
if user_name() <> 'proc_user' then
    --real trigger here
end if;
end;

Now if I cam connected to the DB as DBA and I call the procedure:

call "proc_user"."procedure1"();

Then the trigger will be fired!!

I am also not able to write setuser proc_user within the procedure, so what could be the solution, to let a trigger ignore all inserts coming from a specific procedure?

Thanks in advance

[Baron · Accepted solution]

The best solution (for my case) was to create an event in the name of proc_user, and then call the procedure1 from within this event.

Now, once I want to call the procedure1 I have to trigger the event (and the event in turns will open a new connection in the name of proc_user, and call the procedure1 in the name of proc_user).

Here the trigger can recognize that the transaction is coming from this user (proc_user).

Thanks you all for your hints.

[VolkerBarth]

Have a look at the procedure's SQL SECURITY clause. If you specify SQL SECURITY INVOKER, user_name() will return the user calling the procedure instead of the procedure's owner which will be returned when the default SQL SECURITY DEFINER is used.

Note that if you use INVOKER you should qualify all database objects within the procedure's body and also within procedures/triggers that are called from there. Also note that when your "procedure1" is called from an outer procedure "procedure2" and that has the default SQL SECURITY DEFINER, the owner of "procedure2" will be the invoker of "procedure1".

v17 has added four new special values SESSION_USER, INVOKING_USER, EXECUTING_USER, and PROCEDURE OWNER which help to find out which is which...

[VolkerBarth]

And just to add:

Triggers execute with the privileges of the owner of the according table, not with that of the user whose actions cause the trigger to fire, so they run as "SQL SECURITY DEFINER" by design.

As a consequence, you can influence how procedures and functions behave based on their SQL SECURITY clause - but you cannot for triggers themselves.

[Baron]

Thank you for the reply. Is this available also in Sql Anywhere 10? I get syntax error when I type:

create "proc_user"."procedure1"()

SQL SECURITY DEFINER

Begin

---

End

[VolkerBarth]

No, according to the docs, it was introduced in V11...

For such retrieval, DCX is your friend, it contains the full doc sets from 10.0.1 up to 17...

[Baron]

And is there any alternative for Sql Anywhere 10?

[Vlad]

u-uh?! Yes, any other DB that exists in this world. E.g. Sql Anywhere 17 is a good alternative for SA10.

[VolkerBarth]

Well, apparently you would need to "flag" the particular user in a different fashion, say by

• using the user name from the current connection information (i.e. connection_property('UserID')), or
• supplying an AppInfo value to the connection and distinguish based on that via e.g.connection_property('UserAppInfo')or
• adding a field to your table with different values for that particular user and other users, or ...

[chris_keating]

One option is to use a connection level variable i.e., CREATE VARIABLE _IsProcedure. It can be changed to 1 at the start of the procedure and set back to 0 at the end. The trigger can then process if _isProcedure <> 0 then...

[Baron]

But this does not exclude only transactions connections coming from the procedure! I think in this case all other parallel transactions on the table while the procedure is open will be also considered as trnasactions coming from the procedure (will be excluded in the trigger)!!

[Baron]

does connection_property('UserID') another value than user_name()?

Could you please tell me How I can assign a 'UserAppInfo' to a connection?

[VolkerBarth]

Chris's idea is to use a connection variable to influence whether the trigger does some action or not. You can create and modify such a connection variable anywhere in the course of the connection's livetime, i.e. you can create/set it during a login procedure, with some InitString setting, during some SQL code before you call the procedure or within the procedure itself. And the trigger itself could check whether the variable does exist (via the VAREXISTS function), and only if so and if it is set to a particular value, the trigger would ignore its usual action. I.e. for normal connections, you would not need to use that variable, just for your particular user.

Here's a sample with such a variable used to ignore the usual trigger action...