GRANTs - an enhancement request

Ajay Sahu

GRANTs - an enhancement request
I have a DB2 enhancement request for the IBMers reading this.

Currently we grant select/update access to users/groups/roles at a table
level. This means any new table has to have grants performed on it.
Typically, for us, all tables in a given subsystem/database have grants
performed to the same user/group.

We need a global/system level privilege. So, to borrow the syntax from Sql
Server and Oracle, we would want to say, for example,
GRANT DataReader TO PUBLIC
OR,
GRANT SELECT ANY TABLE TO PUBLIC (Public gets read access on all
tables in the subsystem/database. )

GRANT DataUpdater to JOE
OR,
GRANT UPDATE ANY TABLE TO JOE (Joe gets update access on all
tables in the subsystem/database. )

This would do away the need to grant access to every single table
individually. Also, when the table gets dropped and recreated, the access
would not need to be granted again because it is not tied to the table per se.
An example where this is useful is when we clone PeopleSoft subsystems
using Mainstar. The subsystem clone from prod to test is done in a couple of
hours. But then we spend a few more hours running the GRANTs to test
RACF groups for the tens of thousands of tables that comprise PeopleSoft.

Regards,
Ajay Sahu

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: GRANTs - an enhancement request
(in response to Ajay Sahu)
Ajay

You can do this already with external (ie RACF, ACF2 or Top Secret) security
instead of DB2s own native security mechanisms, so I doubt if it will be
accepted as an enhancement by IBM

Phil G
Grainger Database Solutions

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Ajay Sahu
Sent: 13 January 2010 17:41
To: [login to unmask email]
Subject: [DB2-L] GRANTs - an enhancement request

I have a DB2 enhancement request for the IBMers reading this.

Currently we grant select/update access to users/groups/roles at a table
level. This means any new table has to have grants performed on it.
Typically, for us, all tables in a given subsystem/database have grants
performed to the same user/group.

We need a global/system level privilege. So, to borrow the syntax from Sql
Server and Oracle, we would want to say, for example,
GRANT DataReader TO PUBLIC
OR,
GRANT SELECT ANY TABLE TO PUBLIC (Public gets read access on all
tables in the subsystem/database. )

GRANT DataUpdater to JOE
OR,
GRANT UPDATE ANY TABLE TO JOE (Joe gets update access on all
tables in the subsystem/database. )

This would do away the need to grant access to every single table
individually. Also, when the table gets dropped and recreated, the access
would not need to be granted again because it is not tied to the table per
se.
An example where this is useful is when we clone PeopleSoft subsystems
using Mainstar. The subsystem clone from prod to test is done in a couple of

hours. But then we spend a few more hours running the GRANTs to test
RACF groups for the tens of thousands of tables that comprise PeopleSoft.

Regards,
Ajay Sahu

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Peter Suhner

Re: GRANTs - an enhancement request
(in response to Phil Grainger)

Nice idea and actually what you can get from using external security mechanisms (we use RACF at our site).
Major pros of external security:
- You can use wildcards for object names (thus not only "any table", but also "all tables with naming schema IT*" and the like)
- Security definitions persist even if an object is dropped and recreated

Major disadvantage IMHO is that certain features do not work with external security. The one that hurts us most AUTOREBIND, followed by security check limitations for IMS/TM-DB2 interaction.
I don't know whether these features would work correctly with external security other than RACF, but I very much doubt so.

Regards,
Peter

___________________
Peter Suhner
IBM Petrified DB2 Enveloper
[login to unmask email]


> Date: Wed, 13 Jan 2010 17:40:39 +0000
> From: [login to unmask email]
> Subject: [DB2-L] GRANTs - an enhancement request
> To: [login to unmask email]
>
> I have a DB2 enhancement request for the IBMers reading this.
>
> Currently we grant select/update access to users/groups/roles at a table
> level. This means any new table has to have grants performed on it.
> Typically, for us, all tables in a given subsystem/database have grants
> performed to the same user/group.
>
> We need a global/system level privilege. So, to borrow the syntax from Sql
> Server and Oracle, we would want to say, for example,
> GRANT DataReader TO PUBLIC
> OR,
> GRANT SELECT ANY TABLE TO PUBLIC (Public gets read access on all
> tables in the subsystem/database. )
>
> GRANT DataUpdater to JOE
> OR,
> GRANT UPDATE ANY TABLE TO JOE (Joe gets update access on all
> tables in the subsystem/database. )
>
> This would do away the need to grant access to every single table
> individually. Also, when the table gets dropped and recreated, the access
> would not need to be granted again because it is not tied to the table per se.
> An example where this is useful is when we clone PeopleSoft subsystems
> using Mainstar. The subsystem clone from prod to test is done in a couple of
> hours. But then we spend a few more hours running the GRANTs to test
> RACF groups for the tens of thousands of tables that comprise PeopleSoft.
>
> Regards,
> Ajay Sahu
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
> Webcasts, Conferences- what is going on next?
> RUG leaders- get your events on the calendar today!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_________________________________________________________________
Windows Live: Keep your friends up to date with what you do online.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_1:092010
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Ajay Sahu

Re: GRANTs - an enhancement request
(in response to Peter Suhner)
Phil and Peter,
Thank you for your input. We haven't tried using RACF for this. Another issue
with RACF is that it would only work on z/OS, not LUW.


On Wed, 13 Jan 2010 22:31:25 +0100, Peter Suhner
<[login to unmask email]> wrote:

>
>Nice idea and actually what you can get from using external security
mechanisms (we use RACF at our site).
>Major pros of external security:
>- You can use wildcards for object names (thus not only "any table", but
also "all tables with naming schema IT*" and the like)
>- Security definitions persist even if an object is dropped and recreated
>
>Major disadvantage IMHO is that certain features do not work with external
security. The one that hurts us most AUTOREBIND, followed by security
check limitations for IMS/TM-DB2 interaction.
>I don't know whether these features would work correctly with external
security other than RACF, but I very much doubt so.
>
>Regards,
>Peter
>
>___________________
>Peter Suhner
>IBM Petrified DB2 Enveloper
>[login to unmask email]
>
>
>> Date: Wed, 13 Jan 2010 17:40:39 +0000
>> From: [login to unmask email]
>> Subject: [DB2-L] GRANTs - an enhancement request
>> To: [login to unmask email]
>>
>> I have a DB2 enhancement request for the IBMers reading this.
>>
>> Currently we grant select/update access to users/groups/roles at a table
>> level. This means any new table has to have grants performed on it.
>> Typically, for us, all tables in a given subsystem/database have grants
>> performed to the same user/group.
>>
>> We need a global/system level privilege. So, to borrow the syntax from
Sql
>> Server and Oracle, we would want to say, for example,
>> GRANT DataReader TO PUBLIC
>> OR,
>> GRANT SELECT ANY TABLE TO PUBLIC (Public gets read access on
all
>> tables in the subsystem/database. )
>>
>> GRANT DataUpdater to JOE
>> OR,
>> GRANT UPDATE ANY TABLE TO JOE (Joe gets update access on all
>> tables in the subsystem/database. )
>>
>> This would do away the need to grant access to every single table
>> individually. Also, when the table gets dropped and recreated, the access
>> would not need to be granted again because it is not tied to the table per
se.
>> An example where this is useful is when we clone PeopleSoft
subsystems
>> using Mainstar. The subsystem clone from prod to test is done in a
couple of
>> hours. But then we spend a few more hours running the GRANTs to test
>> RACF groups for the tens of thousands of tables that comprise
PeopleSoft.
>>
>> Regards,
>> Ajay Sahu
>>
>>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L