invoking external function with the definer privileges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steingold@gmail.com - 20 Nov 2005 15:51 GMT

Hi All.
Is it possible to define an external stored procedure to be executed
not with the executing user privileges, but instead with the user who
created the stored procedure privileges in db2 version 8.1 on
linux/unix ?

Thanks in advance
Yaron

Reply to this Message

Ian - 20 Nov 2005 18:11 GMT

> Hi All.
> Is it possible to define an external stored procedure to be executed
> not with the executing user privileges, but instead with the user who
> created the stored procedure privileges in db2 version 8.1 on
> linux/unix ?

Yes, if you write the external procedure using *static* SQL, when the
procedure executes it runs (by default) with the authority of the user
that bound the package.

Good luck,

Reply to this Message

Knut Stolze - 21 Nov 2005 08:57 GMT

>> Hi All.
>> Is it possible to define an external stored procedure to be executed
[quoted text clipped - 5 lines]
> procedure executes it runs (by default) with the authority of the user
> that bound the package.

Or you using dynamic embedded SQL (C code with packages) and the
DYNAMICRULES BIND option when binding the package.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

steingold@gmail.com - 27 Nov 2005 09:00 GMT

> > Hi All.
> > Is it possible to define an external stored procedure to be executed
[quoted text clipped - 7 lines]
>
> Good luck,

Thanks Ian
My UDF takes snapshots and prioritize transactions using IBM C++ API.
I tried to implement your suggestion by wrapping it with another UDF in
C++ that calls it via SQL statement ("select myUDF from
sysibm.sysdummy1") but even though I don't get any error message, the
original UDF does not work for a user without privileges (meaning
calling the wrapper with privileged user works; calling the wrapper
from simple user does not work).

is there another way to override this privileges issue ?

Reply to this Message

Knut Stolze - 28 Nov 2005 08:57 GMT

> My UDF takes snapshots and prioritize transactions using IBM C++ API.

You are aware that there are some snapshot table functions already provided
by DB2? Maybe you could simply use those functions and a little bit of SQL
to achieve the same results.

> I tried to implement your suggestion by wrapping it with another UDF in
> C++ that calls it via SQL statement ("select myUDF from
[quoted text clipped - 4 lines]
>
> is there another way to override this privileges issue ?

You might want to have a look at the registry variable DB2_SNAPSHOT_NOAUTH.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

steingold@gmail.com - 28 Nov 2005 09:50 GMT

Thanks for your reply Knut.

I'm familiar with the snapshot table functions, but I didn't managed to
bypass the privileges needed to prioritize connections. The API I use
(sqlesapr_api() function in version 8.2 and db2Priority in version 8.1)
seems to need a DBA authority.

Reply to this Message

Knut Stolze - 28 Nov 2005 09:54 GMT

> Thanks for your reply Knut.
>
> I'm familiar with the snapshot table functions, but I didn't managed to
> bypass the privileges needed to prioritize connections. The API I use
> (sqlesapr_api() function in version 8.2 and db2Priority in version 8.1)
> seems to need a DBA authority.

Then you should indeed have a look at the NOAUTH registry variable that I
mentioned.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

steingold@gmail.com - 28 Nov 2005 13:14 GMT

I'm not sure I understood you.

The value of DB2_SNAPSHOT_NOAUTH is set to ON, and I restared the
instance.

The UDF is now able to take snapshots, but the invocation of
sqlesapr_api() still fails on privileges (from a privileged user it
works well).

maybe there is anther "NOAUTH varible" beside DB2_SNAPSHOT_NOAUTH?

Reply to this Message

peteh - 28 Nov 2005 13:48 GMT

Hello Yaron;
I know that this doesn't address your problem directly, but wanted to
be sure you were aware of a couple of upcoming developments in 8.2 and
9.x. I too was putting a fair amount of effort into wrapping the admin
api with external routines, procs and table functions, but ran into all
kinds of security-related issues.

When I learned that there is an effort underway to suface virtually all
command line functions via the Admin_Cmd stored proc, I abandoned my
efforts for all but the most critical. There is a list somewhere on
developer works enumerating the commands available in 8.2 and those
slated for v9. Hope you find this useful.

Pete H

Reply to this Message

Knut Stolze - 28 Nov 2005 14:03 GMT

> I'm not sure I understood you.
>
[quoted text clipped - 6 lines]
>
> maybe there is anther "NOAUTH varible" beside DB2_SNAPSHOT_NOAUTH?

I know that the registry variable applies to the table functions. I cannot
say if it is evaluated by the API as well.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

steingold@gmail.com - 28 Nov 2005 14:57 GMT

do you any suggestion for why the static SQL that Ian suggested "select
myUDF from sysibm.sysdummy1" called by the wrapping UDF didn't work ?
According to DB2 documentation this SQL was supposed to be executed
with the definer privileges.

Reply to this Message

Knut Stolze - 28 Nov 2005 15:38 GMT

> do you any suggestion for why the static SQL that Ian suggested "select
> myUDF from sysibm.sysdummy1" called by the wrapping UDF didn't work ?
> According to DB2 documentation this SQL was supposed to be executed
> with the definer privileges.

That is correct. But the API call in not a SQL statement (even if it is
invoked by one), so the privileges applicable to SQL statements do not
apply.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message