>>> 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