Antwort: [DB2-L] Perform Magic

Roy Boxwell

Antwort: [DB2-L] Perform Magic
no magic! Works as designed....here is the part of the SQL manual under
REVOKE

SELECT
Revokes the privilege to create a view or read data from the specified
table or view. A view or a materialized query table is dropped when the
SELECT privilege that was used to
create it is revoked, unless the owner of the view or materialized
query table was directly granted the SELECT privilege from another source
before the view or materialized query
table was created.

explains it quite well I think!

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





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


An: [login to unmask email]
Kopie:
Thema: [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 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