SEO

Environment: DB2 Personal Edition V8 on Windows XP.

If I, as Sysadm, grant a privilege to user Fred WITH GRANT OPTION and then
Fred in turn grants it to Barney, shouldn't both Fred AND Barney lose the
privilege if I revoke it from Fred?

I did this while signed in as Sysadm:
grant select on xyz.department to fred with grant option

When Fred signed in via:
connect to sample user fred using fred
he was able to connect just fine. He was also able to do this:
grant select on xyz.department to barney

When Barney then signed in via:
connect to sample user barney using barney
he was able to read the table:
select * from xyz.department

So far so good, everything is working as expected. Then I signed in as
Sysadm and executed this:
revoke select on xyz.department from fred

As expected, when Fred signed in via:
connect to sample user fred using fred
he was able to connect just fine. However, he failed when he tried to do
this:
select * from xyz.department

Again, this is exactly as expected.

When Barney signed in via:
connect to sample user barney using barney
he connected just fine and had no trouble reading the table:
select * from xyz.department

This was a complete surprise to me! I had always understood that when I give
a privilege to one person with the grant option, and if they then passed the
privileges on to someone else, both the original grantee _and everyone he
had granted the privilege in turn_ would lose the privilege when I revoked
it from the original grantee. At least, that's how it always worked in DB2
on OS/390!!

Are the rules different in DB2 on Windows?? Or have I been wrong all along
in thinking that revokes of privileges granted With Grant Option also revoke
those privileges from everyone who received the privilege from the original
grantee?? Or have I found a bug in DB2 PE for Windows??

I would be very grateful for a clarification of what I am experiencing!

--

Rhino

On Mon, 03 Dec 2007 19:24:46 -0000, rhino
<No.offline.contact.please@anonymous.com> wrote:

> Environment: DB2 Personal Edition V8 on Windows XP.
>
[snipped example]
>
> This was a complete surprise to me! I had always understood that when I
> give
> a privilege to one person with the grant option, and if they then passed
> the
> privileges on to someone else, both the original grantee _and everyone he
> had granted the privilege in turn_ would lose the privilege when I
> revoked
> it from the original grantee. At least, that's how it always worked in
> DB2
> on OS/390!!

It looks like DB2 for z/OS supports "cascaded revokes" [1] whereas DB2 for
LUW does not. The non-cascading behaviour you describe does appear to be
explicitly documented in the "Revoking Privileges" topic [2] of the LUW
Infocenter (see the note at the top of the page).

> Are the rules different in DB2 on Windows?? Or have I been wrong all
> along
> in thinking that revokes of privileges granted With Grant Option also
> revoke
> those privileges from everyone who received the privilege from the
> original
> grantee?? Or have I found a bug in DB2 PE for Windows??
>
> I would be very grateful for a clarification of what I am experiencing!

I've had a quick read through the authorization sections of a draft of the
SQL-2003 standard and I can't find anything in there about "cascaded
revokes" (or any text that appears to describe similar functionality -
although I may have missed it!). Hence, at least according to the
standard, I don't think this is a bug - DB2 for LUW simply has a more
basic implementation of the authorization mechanisms than DB2 for z/OS.

However, knowing how much databases tend to deviate from the standard, I
thought it might be interesting to look at what other databases do in
similar situations:

It would appear that Oracle 11g supports cascaded revokes, at least
according to the last (rather complicated) example in the REVOKE reference
[3]. It also appears that MS SQL Server 2005 implements something similar
with the use of an optional "CASCADE" keyword in its REVOKE statement [4].
MySQL 5.1 ... well, I'm not exactly sure whether MySQL implements this or
not as their GRANT reference [5] just proved too weird for my brain to
cope with (their REVOKE reference states very little). PostgreSQL 8.2 also
supports an optional "CASCADE" keyword in its REVOKE statement [6] which
implements this functionality.

So, on balance, I don't think it's a bug - but it is a rather glaring
omission given the preponderance of support for it in other databases.

References:
[1]
http://publib.boulder.ibm.com/infoce...rvin.htm#rrvin
[2]
http://publib.boulder.ibm.com/infoce...c/t0005806.htm
[3]
http://download.oracle.com/docs/cd/B...0.htm#i2100253
[4] http://msdn2.microsoft.com/en-gb/library/ms187728.aspx
[5] http://dev.mysql.com/doc/refman/5.1/en/grant.html
[6] http://www.postgresql.org/docs/8.2/i...ql-revoke.html


Cheers,

Dave.

"Dave Hughes" <dave@waveform.plus.com> wrote in message
newsp.t2s3kb0gbln7lx@stewie.waveform.plus.com...
> On Mon, 03 Dec 2007 19:24:46 -0000, rhino
> <No.offline.contact.please@anonymous.com> wrote:
>
>> Environment: DB2 Personal Edition V8 on Windows XP.
>>
> [snipped example]
>>
>> This was a complete surprise to me! I had always understood that when I
>> give
>> a privilege to one person with the grant option, and if they then passed
>> the
>> privileges on to someone else, both the original grantee _and everyone he
>> had granted the privilege in turn_ would lose the privilege when I
>> revoked
>> it from the original grantee. At least, that's how it always worked in
>> DB2
>> on OS/390!!
>
> It looks like DB2 for z/OS supports "cascaded revokes" [1] whereas DB2 for
> LUW does not. The non-cascading behaviour you describe does appear to be
> explicitly documented in the "Revoking Privileges" topic [2] of the LUW
> Infocenter (see the note at the top of the page).
>
>> Are the rules different in DB2 on Windows?? Or have I been wrong all
>> along
>> in thinking that revokes of privileges granted With Grant Option also
>> revoke
>> those privileges from everyone who received the privilege from the
>> original
>> grantee?? Or have I found a bug in DB2 PE for Windows??
>>
>> I would be very grateful for a clarification of what I am experiencing!
>
> I've had a quick read through the authorization sections of a draft of the
> SQL-2003 standard and I can't find anything in there about "cascaded
> revokes" (or any text that appears to describe similar functionality -
> although I may have missed it!). Hence, at least according to the
> standard, I don't think this is a bug - DB2 for LUW simply has a more
> basic implementation of the authorization mechanisms than DB2 for z/OS.
>
> However, knowing how much databases tend to deviate from the standard, I
> thought it might be interesting to look at what other databases do in
> similar situations:
>
> It would appear that Oracle 11g supports cascaded revokes, at least
> according to the last (rather complicated) example in the REVOKE reference
> [3]. It also appears that MS SQL Server 2005 implements something similar
> with the use of an optional "CASCADE" keyword in its REVOKE statement [4].
> MySQL 5.1 ... well, I'm not exactly sure whether MySQL implements this or
> not as their GRANT reference [5] just proved too weird for my brain to
> cope with (their REVOKE reference states very little). PostgreSQL 8.2 also
> supports an optional "CASCADE" keyword in its REVOKE statement [6] which
> implements this functionality.
>
> So, on balance, I don't think it's a bug - but it is a rather glaring
> omission given the preponderance of support for it in other databases.
>
> References:
> [1]
> http://publib.boulder.ibm.com/infoce...rvin.htm#rrvin
> [2]
> http://publib.boulder.ibm.com/infoce...c/t0005806.htm
> [3]
> http://download.oracle.com/docs/cd/B...0.htm#i2100253
> [4] http://msdn2.microsoft.com/en-gb/library/ms187728.aspx
> [5] http://dev.mysql.com/doc/refman/5.1/en/grant.html
> [6] http://www.postgresql.org/docs/8.2/i...ql-revoke.html
>
>
Now THAT is a thorough answer to a question! Thanks for going well above and
beyond the call!

The bottom line is that DB2 V8 for Windows appears to be "working as
designed" when it fails to do cascade deletes. Fair enough; at least I know
now!

--

Rhino

Dave Hughes wrote:

> On Mon, 03 Dec 2007 19:24:46 -0000, rhino
> <No.offline.contact.please@anonymous.com> wrote:
>
>> Environment: DB2 Personal Edition V8 on Windows XP.
>>
> [snipped example]
>>
>> This was a complete surprise to me! I had always understood that when
>> I give
>> a privilege to one person with the grant option, and if they then
>> passed the
>> privileges on to someone else, both the original grantee _and everyone he
>> had granted the privilege in turn_ would lose the privilege when I
>> revoked
>> it from the original grantee. At least, that's how it always worked in
>> DB2
>> on OS/390!!
>
> It looks like DB2 for z/OS supports "cascaded revokes" [1] whereas DB2
> for LUW does not. The non-cascading behaviour you describe does appear
> to be explicitly documented in the "Revoking Privileges" topic [2] of
> the LUW Infocenter (see the note at the top of the page).
>
>> Are the rules different in DB2 on Windows?? Or have I been wrong all
>> along
>> in thinking that revokes of privileges granted With Grant Option also
>> revoke
>> those privileges from everyone who received the privilege from the
>> original
>> grantee?? Or have I found a bug in DB2 PE for Windows??
>>
>> I would be very grateful for a clarification of what I am experiencing!
>
> I've had a quick read through the authorization sections of a draft of
> the SQL-2003 standard and I can't find anything in there about "cascaded
> revokes" (or any text that appears to describe similar functionality -
> although I may have missed it!). Hence, at least according to the
> standard, I don't think this is a bug - DB2 for LUW simply has a more
> basic implementation of the authorization mechanisms than DB2 for z/OS.
>
> However, knowing how much databases tend to deviate from the standard, I
> thought it might be interesting to look at what other databases do in
> similar situations:
>
> It would appear that Oracle 11g supports cascaded revokes, at least
> according to the last (rather complicated) example in the REVOKE
> reference [3]. It also appears that MS SQL Server 2005 implements
> something similar with the use of an optional "CASCADE" keyword in its
> REVOKE statement [4]. MySQL 5.1 ... well, I'm not exactly sure whether
> MySQL implements this or not as their GRANT reference [5] just proved
> too weird for my brain to cope with (their REVOKE reference states very
> little). PostgreSQL 8.2 also supports an optional "CASCADE" keyword in
> its REVOKE statement [6] which implements this functionality.
>
> So, on balance, I don't think it's a bug - but it is a rather glaring
> omission given the preponderance of support for it in other databases.
>
> References:
> [1]
> http://publib.boulder.ibm.com/infoce...rvin.htm#rrvin
>
> [2]
> http://publib.boulder.ibm.com/infoce...c/t0005806.htm
>
> [3]
> http://download.oracle.com/docs/cd/B...0.htm#i2100253
>
> [4] http://msdn2.microsoft.com/en-gb/library/ms187728.aspx
> [5] http://dev.mysql.com/doc/refman/5.1/en/grant.html
> [6] http://www.postgresql.org/docs/8.2/i...ql-revoke.html

Just to round out an excellent response:

IBM Informix Dynamic Server automatically revokes privileges granted
under the WITH GRANT OPTION without requiring the CASCADE keyword, and
without having an explicit RESTRICT or other keyword to prevent it
happening. I note that this is not compatible with SQL-92; I suspect it
was compatible with SQL-86, which was probably current when the
functionality was added to Informix.

See: http://publib.boulder.ibm.com/infoce...v111/index.jsp

You'll find the SQL Syntax manual buried under Designing and 'Designing
SQL Statements' on the LHS menu.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-1958 ripemd256 2007-12-06 06:00:04
291142CDA3E796920097E4B1EC83CA8EE52BF647F21CE35543 7E96E821EFEDC2