SET SESSION AUTHORIZATION

[zbychu]

Hi,

I have a problem with a special sql.
My configuration : IBM DB2 V8.1.5 / AIX

Procedure:
CREATE PROCEDURE DB2TARAN.SetSessionAutor()
SPECIFIC x.SetSessionAutor
LANGUAGE SQL
P1: BEGIN
SET SESSION AUTHORIZATION user ;END P1

SQL0428N The SQL statement is only
allowed as the first statement in a
unit of work.

Thanks in advance

[Serge Rielau]

zbychu wrote:

Hi,

I have a problem with a special sql.
My configuration : IBM DB2 V8.1.5 / AIX

Procedure:
CREATE PROCEDURE DB2TARAN.SetSessionAutor()
SPECIFIC x.SetSessionAutor
LANGUAGE SQL
P1: BEGIN
SET SESSION AUTHORIZATION user ;END P1

SQL0428N The SQL statement is only
allowed as the first statement in a
unit of work.

Thanks in advance

Interesting I never thought of this register being set in a proc, but it
does make sense...
Do you get the error message from CREATE PROCEDURE or from CALL.
The fisrt statement in your transaction would be the CALL. So the
error message is correct.
You should(!) be able to straighten things out by adding the COMMIT
before the SET inside the procedure.
If that doesn't work I would consider it a bug.
You could then try EXECUTE IMMEDIATE SET .... .
That of course would require the invoker of the procedure to be DBADM or
you will get an authorization error.

Cheers
Serge

[zbychu]

What the problem is:
user have GRANT to tables SELECT only
and EXECUTE to procedures -> one way to INSERT and UPDATE
bat if DYNAMIC SQL in procedure included 'INSERT ...' ,
the error SQL0551N
I try by SET AUTHORIZATION set temporary "grant" on user
Is another way ?
Serge Rielau wrote:

zbychu wrote:

Hi,

I have a problem with a special sql.
My configuration : IBM DB2 V8.1.5 / AIX

Procedure:
CREATE PROCEDURE DB2TARAN.SetSessionAutor()
SPECIFIC x.SetSessionAutor
LANGUAGE SQL
P1: BEGIN
SET SESSION AUTHORIZATION user ;END P1

SQL0428N The SQL statement is only
allowed as the first statement in a
unit of work.

Thanks in advance

Interesting I never thought of this register being set in a proc, but it
does make sense...
Do you get the error message from CREATE PROCEDURE or from CALL.
The fisrt statement in your transaction would be the CALL. So the
error message is correct.
You should(!) be able to straighten things out by adding the COMMIT
before the SET inside the procedure.
If that doesn't work I would consider it a bug.
You could then try EXECUTE IMMEDIATE SET .... .
That of course would require the invoker of the procedure to be DBADM or
you will get an authorization error.

Cheers
Serge

[Serge Rielau]

zbychu wrote:

What the problem is:
user have GRANT to tables SELECT only
and EXECUTE to procedures -> one way to INSERT and UPDATE
bat if DYNAMIC SQL in procedure included 'INSERT ...' ,
the error SQL0551N
I try by SET AUTHORIZATION set temporary "grant" on user
Is another way ?
Serge Rielau wrote:

zbychu wrote:

Hi,

I have a problem with a special sql.
My configuration : IBM DB2 V8.1.5 / AIX

Procedure:
CREATE PROCEDURE DB2TARAN.SetSessionAutor()
SPECIFIC x.SetSessionAutor
LANGUAGE SQL
P1: BEGIN
SET SESSION AUTHORIZATION user ;END P1

SQL0428N The SQL statement is only
allowed as the first statement in a
unit of work.

Thanks in advance

Interesting I never thought of this register being set in a proc, but
it does make sense...
Do you get the error message from CREATE PROCEDURE or from CALL.
The fisrt statement in your transaction would be the CALL. So the
error message is correct.
You should(!) be able to straighten things out by adding the COMMIT
before the SET inside the procedure.
If that doesn't work I would consider it a bug.
You could then try EXECUTE IMMEDIATE SET .... .
That of course would require the invoker of the procedure to be DBADM
or you will get an authorization error.

Cheers
Serge

I see. Take a look at the BIND/PREP options for "DYNAMIC RULLES".
There is a procedure SET_ROUTINE_OPTS() which you can use to set options
for SQL Procedures. I have never tried that though.

Cheers
Serge