469,942 Members | 2,576 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Dumb security question

Hi All;
In DB2 LUW 8.2 (and v9 for that matter):
* I have a table myTable
* I have a group STAFF
* I have a user USER1 who is a member of group STAFF
* Group STAFF has SELECT and/or SELECT WITH GRANT OPTION on myTable

Can USER1 create a stored proc that selects from myTable? Or do I have
to explicitly GRANT SELECT on myTable to USER1? Is there any way to
avoid explicit grants to individual users on every object they might
write a procedure against? In production, that's not such a big deal,
but for development environment it seems like ALOT of administration...
Thanks for any thoughts on the subject.

Pete H

Jan 22 '07 #1
5 1338
peteh wrote:
Hi All;
In DB2 LUW 8.2 (and v9 for that matter):
* I have a table myTable
* I have a group STAFF
* I have a user USER1 who is a member of group STAFF
* Group STAFF has SELECT and/or SELECT WITH GRANT OPTION on myTable

Can USER1 create a stored proc that selects from myTable? Or do I have
to explicitly GRANT SELECT on myTable to USER1? Is there any way to
avoid explicit grants to individual users on every object they might
write a procedure against? In production, that's not such a big deal,
but for development environment it seems like ALOT of administration...
Thanks for any thoughts on the subject.
Since DB2 does not control OS groups it has no means to act if a user
falls out of a group. Thus group privileges are only used for dynamic SQL.
Since you are dealing with a development environment would PUBLIC be an
option?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Jan 22 '07 #2
"peteh" <ph******@intellicare.comwrote in message
news:11*********************@51g2000cwl.googlegrou ps.com...
Hi All;
In DB2 LUW 8.2 (and v9 for that matter):
* I have a table myTable
* I have a group STAFF
* I have a user USER1 who is a member of group STAFF
* Group STAFF has SELECT and/or SELECT WITH GRANT OPTION on myTable

Can USER1 create a stored proc that selects from myTable? Or do I have
to explicitly GRANT SELECT on myTable to USER1? Is there any way to
avoid explicit grants to individual users on every object they might
write a procedure against? In production, that's not such a big deal,
but for development environment it seems like ALOT of administration...
Thanks for any thoughts on the subject.

Pete H
I believe that to create a SP, you need BINDADD, which granted by default to
public. To execute a SP, you need execute authority on the SP (unless you
created it).

To the best of my recollection, if USER1 has select authority (via group
privilege) on the table, and has bindadd, then they should be able to create
the SP.
Jan 22 '07 #3
Raj
I think, at compile time db2 will use user privileges
Mark A wrote:
>
To the best of my recollection, if USER1 has select authority (via group
privilege) on the table, and has bindadd, then they should be able to create
the SP.
Jan 23 '07 #4

Serge Rielau wrote:
Since you are dealing with a development environment would PUBLIC be an
option?
Yes, this is OK in a dev environment. Just wanted to make sure I wasn't
overlooking something before recommending this as a new standard. It
seemed odd that group auths weren't considered, but I didn't make the
connection to the OS-level security model. Thanks Serge.

Pete H

Jan 23 '07 #5
Careful here. BINDADD gives you the privilege to create a a package.
To do so one must have the EXPLICIT privilege to execute each and every sql
stmt. in the package.
Group privileges are implicit (apart from PUBLIC which is explicit).
So having BINDADD and the explicit privilege to do the sql is required to
generate any package.

USER1 will not be able to generate the package for the proc as he does not
have explicit SELECT privilege on myTable.
USER1 could execute the proc if given execute on the proc.
Regards, Pierre.
PS: same applies for creating views.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Mark A" <no****@nowhere.coma écrit dans le message de news:
lZ******************************@comcast.com...
"peteh" <ph******@intellicare.comwrote in message
news:11*********************@51g2000cwl.googlegrou ps.com...
>Hi All;
In DB2 LUW 8.2 (and v9 for that matter):
* I have a table myTable
* I have a group STAFF
* I have a user USER1 who is a member of group STAFF
* Group STAFF has SELECT and/or SELECT WITH GRANT OPTION on myTable

Can USER1 create a stored proc that selects from myTable? Or do I have
to explicitly GRANT SELECT on myTable to USER1? Is there any way to
avoid explicit grants to individual users on every object they might
write a procedure against? In production, that's not such a big deal,
but for development environment it seems like ALOT of administration...
Thanks for any thoughts on the subject.

Pete H

I believe that to create a SP, you need BINDADD, which granted by default
to public. To execute a SP, you need execute authority on the SP (unless
you created it).

To the best of my recollection, if USER1 has select authority (via group
privilege) on the table, and has bindadd, then they should be able to
create the SP.
Jan 23 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by squash | last post: by
15 posts views Thread by Good Man | last post: by
2 posts views Thread by Marcus Smaby | last post: by
3 posts views Thread by ed | last post: by
2 posts views Thread by WebBuilder451 | last post: by
13 posts views Thread by PJ6 | last post: by
reply views Thread by Ty | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.