SECURITY INVOKER in DB2?

[Troels Arvin]

Hello,

In PostgreSQL, when you create a function, you may choose between SECURITY
INVOKER or SECURITY DEFINER:
http://www.postgresql.org/docs/8.2/s...efunction.html
When a function has SECURITY INVOKER (the default), the SQL it contains is
run with the permissions of the invoking user.

It seems that DB2 UDFs uses SECURITY DEFINER (using PostgreSQL terms), and
that there is no way to change that. Or? Is there a way for me to provide
a UDF to a lot of users but prevent the contained SQL from being executed
with my permissions?

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/

[Serge Rielau]

Troels Arvin wrote:

Hello,

In PostgreSQL, when you create a function, you may choose between SECURITY
INVOKER or SECURITY DEFINER:
http://www.postgresql.org/docs/8.2/s...efunction.html
When a function has SECURITY INVOKER (the default), the SQL it contains is
run with the permissions of the invoking user.

It seems that DB2 UDFs uses SECURITY DEFINER (using PostgreSQL terms), and
that there is no way to change that. Or? Is there a way for me to provide
a UDF to a lot of users but prevent the contained SQL from being executed
with my permissions?

You can use dynamic SQL for the pieces you don't want to vouch for.
To use dynamic SQL in a SQL UDF CALL a procedure and place the
PREPARE/EXECUTE/EXECUTE IMMEDIATE in there.
Dynamic SQL runs under invoker rights.

Still curiosu to learn more about what is driving this.
Encapsulation of authority is a core function of views and routines.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab