The missing view...

Vermeulen Tim

The missing view...
Today we had a missing view...
We found out how it got dropped, but I'd like to hear some expert opinions on this...

"The story"

Long time ago, a view was created something like this:

CREATE VIEW OWNER1.V_VIEW ( COL1, COL2, COL3) AS (
SELECT A.COL1, A.COL2, A.COL3 FROM OWNER1.MY1STTB A
UNION ALL
SELECT B.COL1, B.COL2, B.COL3 FROM OWNER2.OTHERTB B
UNION ALL
SELECT C.COL1, C.COL2, C.COL3 FROM OWNER1.MY2NDTB C
);


years have gone by, and then...
to clean up things, someone named OWNER2 decides to perform a REVOKE, followed by GRANTs on his tables.
(original grants where given by sysadm 'blabla', which owner2 doesn't like in systabauth tables)

REVOKE DELETE, INSERT, UPDATE, SELECT
ON TABLE OWNER2.OTHERTB
FROM OWNER1 BY BLABLA;
RESULT OF SQL STATEMENT:
DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
DSNT418I SQLSTATE = 00000 SQLSTATE RETURN CODE
DSNT416I SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION
REVOKE SUCCESSFUL

Things go well and owner2 decides to go to home and live happily ever after...

But then, someone finds out V_VIEW is missing ?!?




By doing the revoke, the view got dropped. (no other reasons could be found...)
However, owner2 had no idea he was doing this ....

In my humble opinion, I would expect at least a warning after the revoke ??
Or am I missing something here??



kind regards,
Tim Vermeulen

(and it's not even friday yet...)














______________________________________________________________________
This message is explicitly subject to the conditions of the e-mail disclaimer: www.sdworx.com/emaildisclaimer
If you are unable to consult this e-mail disclaimer, please notify the sender at once.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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

Pete Woodman

Re: The missing view...
(in response to Vermeulen Tim)
Tim,

See the Admin Guide under "Other Implications of the REVOKE statement" in section "Views Created By SYSADM" - it's explained in there.

Not ideal, but Working As Designed!

Cheers

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

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