469,609 Members | 1,664 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

invoking external function with the definer privileges

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

Nov 20 '05 #1
11 1926
Ian
st*******@gmail.com wrote:
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,

Nov 20 '05 #2
Ian wrote:
st*******@gmail.com wrote:
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.


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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 21 '05 #3
Ian wrote:
st*******@gmail.com wrote:
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,


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 ?

Nov 27 '05 #4
st*******@gmail.com wrote:
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
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 ?


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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 28 '05 #5
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.

Nov 28 '05 #6
st*******@gmail.com wrote:
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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 28 '05 #7
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?

Nov 28 '05 #8
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

Nov 28 '05 #9
st*******@gmail.com wrote:
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?


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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 28 '05 #10
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.

Nov 28 '05 #11
st*******@gmail.com wrote:
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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 28 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Marvin McNett | last post: by
reply views Thread by Solution2021 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.