SEO

#1 (**permalink**) 03-20-2008, 12:47 PM

>>> On 3/18/2008 at 11:15 AM, in message
<64abl8F2b7lv2U1@mid.individual.net>,
Serge Rielau<srielau@ca.ibm.com> wrote:
> Gladiator wrote:
>> Hi ,
>>
>> Can someone tell me how do i grant a Read privilage on all the tables
>> in a schema X to a different user who is not the owner of the Schema .
>> I dont want to grant him the Alterin privilage to the user.I just want
>> the user to read the tables in a schema for which he is not the owner.
>>
>> I have more than 100 tables with different names. so it is difficult
>> to give grant select on each table.
> Now, now.. it's not THAT hard.... (untested)
>
> db2 "SELECT 'GRANT SELECT ON "' || tabschema || '"."' || tabname || '"
> TO USER joe' FROM SYSCAT.TABLES WHERE tabschema = 'JILL'" > grant.ddl
> db2 -tvf grant.ddl

Thanks to Gladiator for bringing up this issue, because it has also been on
my mind.

The "problem" with Serge's answer is that any tables created after this
script is run will still require GRANT to be done for each new table.

The reason I put quotes around "problem" is I am fairly certain that this is
quite intentional, in that for "security" reasons DB2 wants a DBA or the
link to explictly grant rights to new tables that are created. I understand
that, though I'm not sure I agree. But since I'm not the DBA that has to do
all of the 'extra work' this causes perhaps I shouldn't care. :-)

Frank

#2 (**permalink**) 03-20-2008, 12:47 PM

Frank Swarbrick wrote:
>>>> On 3/18/2008 at 11:15 AM, in message
> <64abl8F2b7lv2U1@mid.individual.net>,
> Serge Rielau<srielau@ca.ibm.com> wrote:
>> Gladiator wrote:
>>> Hi ,
>>>
>>> Can someone tell me how do i grant a Read privilage on all the tables
>>> in a schema X to a different user who is not the owner of the Schema .
>>> I dont want to grant him the Alterin privilage to the user.I just want
>>> the user to read the tables in a schema for which he is not the owner.
>>>
>>> I have more than 100 tables with different names. so it is difficult
>>> to give grant select on each table.
>> Now, now.. it's not THAT hard.... (untested)
>>
>> db2 "SELECT 'GRANT SELECT ON "' || tabschema || '"."' || tabname || '"
>> TO USER joe' FROM SYSCAT.TABLES WHERE tabschema = 'JILL'" > grant.ddl
>> db2 -tvf grant.ddl
>
> Thanks to Gladiator for bringing up this issue, because it has also been on
> my mind.
>
> The "problem" with Serge's answer is that any tables created after this
> script is run will still require GRANT to be done for each new table.
>
> The reason I put quotes around "problem" is I am fairly certain that this is
> quite intentional, in that for "security" reasons DB2 wants a DBA or the
> link to explictly grant rights to new tables that are created. I understand
> that, though I'm not sure I agree. But since I'm not the DBA that has to do
> all of the 'extra work' this causes perhaps I shouldn't care. :-)
DB2 supports what you are asking for for routines, btw:
http://publib.boulder.ibm.com/infoce.../r0007699.html

So the reason is indeed not technical. It's ideological..
which is worse if you want to change it ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#3 (**permalink**) 03-28-2008, 04:39 AM

>>> On 3/19/2008 at 2:20 PM, in message
<64datbF2b0nkmU1@mid.individual.net>,
Serge Rielau<srielau@ca.ibm.com> wrote:
> Frank Swarbrick wrote:
>>>>> On 3/18/2008 at 11:15 AM, in message
>> <64abl8F2b7lv2U1@mid.individual.net>,
>> Serge Rielau<srielau@ca.ibm.com> wrote:
>>> Gladiator wrote:
>>>> Hi ,
>>>>
>>>> Can someone tell me how do i grant a Read privilage on all the tables
>>>> in a schema X to a different user who is not the owner of the Schema .
>>>> I dont want to grant him the Alterin privilage to the user.I just want
>>>> the user to read the tables in a schema for which he is not the owner.
>>>>
>>>> I have more than 100 tables with different names. so it is difficult
>>>> to give grant select on each table.
>>> Now, now.. it's not THAT hard.... (untested)
>>>
>>> db2 "SELECT 'GRANT SELECT ON "' || tabschema || '"."' || tabname || '"
>>> TO USER joe' FROM SYSCAT.TABLES WHERE tabschema = 'JILL'" > grant.ddl
>>> db2 -tvf grant.ddl
>>
>> Thanks to Gladiator for bringing up this issue, because it has also been

> on
>> my mind.
>>
>> The "problem" with Serge's answer is that any tables created after this
>> script is run will still require GRANT to be done for each new table.
>>
>> The reason I put quotes around "problem" is I am fairly certain that
> this is
>> quite intentional, in that for "security" reasons DB2 wants a DBA or the
>> link to explictly grant rights to new tables that are created. I
> understand
>> that, though I'm not sure I agree. But since I'm not the DBA that has
> to do
>> all of the 'extra work' this causes perhaps I shouldn't care. :-)
> DB2 supports what you are asking for for routines, btw:
> http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
> uw.sql.ref.doc/doc/r0007699.html
>
> So the reason is indeed not technical. It's ideological..
> which is worse if you want to change it ;-)

I haven't tried it yet, but it looks like I can say something to the effect
of
GRANT EXECUTE ON PROCEDURE exeuser.* TO myuser;
And not only will I have rights to all existing packages in the "exeuser"
schema, but I will also have rights to anything created in that schema in
the future. Great! This is exactly what I was looking for and not able to
find. (I must not have looked very hard, huh?)

As long as it's technologially possible I am happy. If the DBA chooses to
do more work and do it on a package by package basis that's not my concern.

Thanks again! I love this group.

Frank

#4 (**permalink**) 03-28-2008, 04:39 AM

>>> On 3/20/2008 at 2:27 PM, in message
<47E2743A.6F0F.0085.0@efirstbank.com>,
Frank Swarbrick<Frank.Swarbrick@efirstbank.com> wrote:
>>>> On 3/19/2008 at 2:20 PM, in message
> <64datbF2b0nkmU1@mid.individual.net>,
> Serge Rielau<srielau@ca.ibm.com> wrote:
>> Frank Swarbrick wrote:
>>>>>> On 3/18/2008 at 11:15 AM, in message
>>> <64abl8F2b7lv2U1@mid.individual.net>,
>>> Serge Rielau<srielau@ca.ibm.com> wrote:
>>>> Gladiator wrote:
>>>>> Hi ,
>>>>>
>>>>> Can someone tell me how do i grant a Read privilage on all the
tables
>>>>> in a schema X to a different user who is not the owner of the Schema
..
>>>>> I dont want to grant him the Alterin privilage to the user.I just
want
>>>>> the user to read the tables in a schema for which he is not the
owner.
>>>>>
>>>>> I have more than 100 tables with different names. so it is difficult
>>>>> to give grant select on each table.
>>>> Now, now.. it's not THAT hard.... (untested)
>>>>
>>>> db2 "SELECT 'GRANT SELECT ON "' || tabschema || '"."' || tabname || '"

>>>> TO USER joe' FROM SYSCAT.TABLES WHERE tabschema = 'JILL'" > grant.ddl
>>>> db2 -tvf grant.ddl
>>>
>>> Thanks to Gladiator for bringing up this issue, because it has also
been
>
>> on
>>> my mind.
>>>
>>> The "problem" with Serge's answer is that any tables created after this
>>> script is run will still require GRANT to be done for each new table.
>>>
>>> The reason I put quotes around "problem" is I am fairly certain that
>> this is
>>> quite intentional, in that for "security" reasons DB2 wants a DBA or
the
>>> link to explictly grant rights to new tables that are created. I
>> understand
>>> that, though I'm not sure I agree. But since I'm not the DBA that has
>> to do
>>> all of the 'extra work' this causes perhaps I shouldn't care. :-)
>> DB2 supports what you are asking for for routines, btw:
>> http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l

>
>> uw.sql.ref.doc/doc/r0007699.html
>>
>> So the reason is indeed not technical. It's ideological..
>> which is worse if you want to change it ;-)
>
> I haven't tried it yet, but it looks like I can say something to the
> effect
> of
> GRANT EXECUTE ON PROCEDURE exeuser.* TO myuser;
> And not only will I have rights to all existing packages in the
> "exeuser"
> schema, but I will also have rights to anything created in that schema
> in
> the future. Great! This is exactly what I was looking for and not able
> to
> find. (I must not have looked very hard, huh?)
>
> As long as it's technologially possible I am happy. If the DBA chooses
> to
> do more work and do it on a package by package basis that's not my
> concern.
>
> Thanks again! I love this group.

Oh hell, I missunderstood. It looks like this feature is available for
PROCEDURES, FUNCTIONS and METHODS, but not for PACKAGES. Which I'm sure is
why you said it was an ideological issue and not a technological issue.

Do you think that if a PMR was made to allow this for packages that IBM
would consider implementing it?

Frank

#5 (**permalink**) 03-28-2008, 04:39 AM

Frank Swarbrick wrote:
> Oh hell, I missunderstood. It looks like this feature is available for
> PROCEDURES, FUNCTIONS and METHODS, but not for PACKAGES. Which I'm sure is
> why you said it was an ideological issue and not a technological issue.
>
> Do you think that if a PMR was made to allow this for packages that IBM
> would consider implementing it?
It needs a carrot and whip. "Invonvenience" makes for a bad incentive.

Now we have drifted a bit from the original request. The OP was asking
for table-objects. You are asking for package objects.
This is embedded SQL in C, Cobol, ... applications?
Because if it's about external routines then the schema.* approach works
on the routine level.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#6 (**permalink**) 03-28-2008, 04:39 AM

>>> On 3/21/2008 at 12:34 PM, in message
<64ideqF2bn43lU1@mid.individual.net>,
Serge Rielau<srielau@ca.ibm.com> wrote:
> Frank Swarbrick wrote:
>> Oh hell, I missunderstood. It looks like this feature is available for
>> PROCEDURES, FUNCTIONS and METHODS, but not for PACKAGES. Which I'm sure

> is
>> why you said it was an ideological issue and not a technological issue.
>>
>> Do you think that if a PMR was made to allow this for packages that IBM
>> would consider implementing it?
> It needs a carrot and whip. "Invonvenience" makes for a bad incentive.
>
> Now we have drifted a bit from the original request. The OP was asking
> for table-objects. You are asking for package objects.
> This is embedded SQL in C, Cobol, ... applications?
> Because if it's about external routines then the schema.* approach works
> on the routine level.

Yes, I think I have drifted. Seems to me that allowing GRANT of any type of
object for schema.* would be useful.
In my most recent message I was referring to packages created by embedded
SQL (Cobol) applications. But I would think it would be useful for tables,
views, and other things as well.

But if it's the general philosophy that these priviliges should be granted
on an explicit one-by-one basis, well, who am I to argue. (Rhetorical
question. I'll argue about anything. <g>)

Frank

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

SEO

Re: Schema Read privilage