Perform Magic

Michael Bednarz

Perform Magic
Hi folks and SQL experts,

Here is the magic SQL:

SET CURRENT SQLID = 'SYSDB';
DROP TABLE TTEST; (Only when exist)
COMMIT;
CREATE TABLE TTEST ( F1 CHAR(1));
GRANT SELECT ON TTEST TO PUBLIC;
COMMIT;
GRANT INSERT ON TTEST TO PUBLIC;
COMMIT;
GRANT UPDATE ON TTEST TO PUBLIC;
COMMIT;
SET CURRENT SQLID = 'HUGO';
CREATE VIEW VTEST AS SELECT * FROM SYSDB.TTEST;
SET CURRENT SQLID = 'SYSDB'; (Maybe obsolet) (Didn't change the result)
GRANT SELECT ON TTEST TO HUGO; (Maybe obsolet) (Didn't change the result)
SELECT * FROM HUGO.VTEST; (SQLCODE 100)
SET CURRENT SQLID = 'SYSDB';
REVOKE SELECT ON TTEST FROM PUBLIC;
SELECT * FROM HUGO.VTEST;

DSNT408I SQLCODE = -204, ERROR: HUGO.VTEST IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION


Can someone explain why the view disapears without a drop!

Regards

Michael Bednarz

Citicorp Operations Consulting GmbH
Düsseldorfer Str. 71 b
40667 Meerbusch
Tel. +49 2132 74 259
mailto:[login to unmask email]

Citicorp Operations Consulting GmbH
Sitz der Gesellschaft: Meerbusch u Handelsregister Amtsgericht Neuss HRB 7985
Geschäftsführer: Andreas Hamm, Peter Kapic, Peter Klein, Ronny Vyncke
USt-ID-Nr.: DE 161 519 172 u Steuer-Nr.: 47/220/21888

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Raymond Bell

Re: Perform Magic
(in response to Michael Bednarz)
I imagine there's a few people that can, but you got me at a good time. The authority required to create a view is simply Select on the objects the view is on. If that Select privilege is revoked, so are your rights to have created that view, so it gets implicitly dropped. Can make for exciting times...



Hope that helps - with why what happened happened, at least.



Happy Friday,





Raymond



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Bednarz, Michael
Sent: 12 December 2008 09:40
To: [login to unmask email]
Subject: [DB2-L] Perform Magic



Hi folks and SQL experts,

Here is the magic SQL:

SET CURRENT SQLID = 'SYSDB';
DROP TABLE TTEST; (Only when exist)
COMMIT;
CREATE TABLE TTEST ( F1 CHAR(1));
GRANT SELECT ON TTEST TO PUBLIC;
COMMIT;
GRANT INSERT ON TTEST TO PUBLIC;
COMMIT;
GRANT UPDATE ON TTEST TO PUBLIC;
COMMIT;
SET CURRENT SQLID = 'HUGO';
CREATE VIEW VTEST AS SELECT * FROM SYSDB.TTEST;
SET CURRENT SQLID = 'SYSDB'; (Maybe obsolet) (Didn't change the result)
GRANT SELECT ON TTEST TO HUGO; (Maybe obsolet) (Didn't change the result)
SELECT * FROM HUGO.VTEST; (SQLCODE 100)
SET CURRENT SQLID = 'SYSDB';
REVOKE SELECT ON TTEST FROM PUBLIC;
SELECT * FROM HUGO.VTEST;

DSNT408I SQLCODE = -204, ERROR: HUGO.VTEST IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION



Can someone explain why the view disapears without a drop!

Regards

Michael Bednarz

Citicorp Operations Consulting GmbH
Düsseldorfer Str. 71 b
40667 Meerbusch
Tel. +49 2132 74 259
mailto:[login to unmask email] <mailto:[login to unmask email]>

Citicorp Operations Consulting GmbH
Sitz der Gesellschaft: Meerbusch u Handelsregister Amtsgericht Neuss HRB 7985
Geschäftsführer: Andreas Hamm, Peter Kapic, Peter Klein, Ronny Vyncke
USt-ID-Nr.: DE 161 519 172 u Steuer-Nr.: 47/220/21888



________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Adlersburg

Re: Perform Magic
(in response to Raymond Bell)
Hi,

As soon as you revoke the privilege from the group (in your case PUBLIC)
under which a view has been created, the view is dropped implicitly. At the
time of view-creation HUGO runs the CREATE as a member of the group
PUBLIC. The following SQL-code works properly:

SET CURRENT SQLID = 'SYSDB';
DROP TABLE TTEST ;
COMMIT ;
CREATE TABLE TTEST ( F1 CHAR(1));
COMMIT ;
GRANT SELECT ON TTEST TO HUGO;
COMMIT ;
GRANT SELECT ON TTEST TO PUBLIC;
COMMIT;
GRANT INSERT ON TTEST TO PUBLIC;
COMMIT;
GRANT UPDATE ON TTEST TO PUBLIC;
COMMIT;
SET CURRENT SQLID = 'HUGO';
CREATE VIEW VTEST AS SELECT * FROM SYSDB.TTEST;
SET CURRENT SQLID = 'SYSDB';
SELECT * FROM HUGO.VTEST;
SET CURRENT SQLID = 'SYSDB';
REVOKE SELECT ON TTEST FROM PUBLIC;
SELECT * FROM HUGO.VTEST;

kind regards,

peter

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms