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