Antwort: Re: [DB2-L] Perform Magic

Roy Boxwell

Antwort: Re: [DB2-L] Perform Magic
and I thought I was fast at typing!!! Raymond is superschnell!



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert





"Bell, Raymond" <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
12.12.2008 10:53
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: Re: [DB2-L] Perform Magic


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]
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
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 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


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
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 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


______________________________________________________________________

* 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: Antwort: Re: [DB2-L] Perform Magic
(in response to Roy Boxwell)
I knew my Pitmans Elementary Typing Certificate would come in handy one day. For ridicule, mostly, but that's another story. Still, at the time there were 40 students and one bloke (yours truly) so, as a colleague of mine would say, do the math!





Raymond



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Roy Boxwell
Sent: 12 December 2008 11:20
To: [login to unmask email]
Subject: [DB2-L] Antwort: Re: [DB2-L] Perform Magic




and I thought I was fast at typing!!! Raymond is superschnell!



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




"Bell, Raymond" <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>

12.12.2008 10:53
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: Re: [DB2-L] Perform Magic




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]

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 - 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 - 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

Michael Bednarz

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

Thank you all for your comments and explains. Helped verry well.

Regards
Michael

______________________________________________________________________

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




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html