[DB2-L] Perform Magic

Walter Janißen

[DB2-L] Perform Magic
Hi Michael

Nice to hear from you

DB2 is working as usual. If a user has select-privilege on a table, he can create views which he owns. If the select privilege is revoked, the view will be dropped.
This is working I think since the beginning of DB2

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996




________________________________

Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Bednarz, Michael
Gesendet: Freitag, 12. Dezember 2008 10:40
An: [login to unmask email]
Betreff: [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

Balachandran Chandrasekaran1

Re: Perform Magic
(in response to Walter Janißen)
This is an example of timestamp based privileges. Your Grant to public
helped in the creation of HUGO. So, when you revoked the GRANT from public,
the view got dropped though you had granted SELECT privilege for HUGO
explicitly (Note: timestamp of this grant is latter than the Creation of
the HUGO.TTEST view). I think this is the way DB2 has been working, checks
the grantor and timestamp always. If your GRANT on HUGO had preceeded the
CREATE VIEW, the view could have survived.

This is just another feature, i think, like revoking a privilege is
possible by only the user who had actually done the GRANT.




"Bednarz,
Michael"
<michael.bednarz@ To
CITI.COM> [login to unmask email]
Sent by: DB2 Data cc
Base Discussion
List Subject
<[login to unmask email] [DB2-L] Perform Magic
ORG>


12/12/2008 15:10


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






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