SEO

#1 (**permalink**) 02-27-2008, 11:31 AM

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

#2 (**permalink**) 02-27-2008, 11:31 AM

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

#3 (**permalink**) 02-27-2008, 11:31 AM

"peteh" <phazzard@intellicare.com> wrote in message
news:1169502838.136080.29320@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.

#4 (**permalink**) 02-27-2008, 11:32 AM

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.

#5 (**permalink**) 02-27-2008, 11:32 AM

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

#6 (**permalink**) 02-27-2008, 11:32 AM

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" <nobody@nowhere.com> a écrit dans le message de news:
lZidnbGi7efL2SjYnZ2dnUVZ_sqdnZ2d@comcast.com...
> "peteh" <phazzard@intellicare.com> wrote in message
> news:1169502838.136080.29320@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.
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Dumb security question