Owner can't DROP TABLE in DB2 v8 compatibility mode

Maciej Poraszka

Owner can't DROP TABLE in DB2 v8 compatibility mode
I have a strange problem in DB2 8 running in compatibility mode.

Subsystem has RACF access control module ([login to unmask email]) VERSION OF PK08127
installed.

When user TST runs following SQLs :

SET CURRENT SQLID='ABC';
COMMIT;
CREATE GLOBAL TEMPORARY TABLE ABC.TESTGLOB LIKE ABC.TEST;

COMMIT;
SET CURRENT SQLID='ABC';
COMMIT;
DROP TABLE ABC.TESTGLOB;
COMMIT;

he gets :

DROP TABLE ABC.TESTGLOB

SQL ERROR DURING EXECUTE
IMMEDIATE
DSNT408I SQLCODE = -551, ERROR: ABC DOES NOT HAVE THE PRIVILEGE TO PERFORM
OPERATION DROP TABLE ON OBJECT
ABC.TESTGLOB

DSNT418I SQLSTATE = 42501 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION


and accompanying RACF message :

ICH408I USER(TST ) GROUP(TESTZ ) NAME(xxx )
DBPX.DSNDB06.DBADM CL(DSNADM )
INSUFFICIENT ACCESS AUTHORITY
FROM DBPX.*.DBADM (G)
ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )

Why ?

Isn't ABC an owner of a table TESTGLOB ?
And as an owner he should have rights of dropping it ...
ABC is a RACF group and user TST submitting a job is a member of this group.

I get the same problem ( -551 ) when I try to DROP ALIAS. No problems with
dropping regular tables.

Above problems don't exist in DB2 v8 subsytem running in new-function mode
with the same level of RACF exit.
In DB2 v 7 also everything works fine as expected. RACF definitions are all
the same in all subsytems.

Any suggestions ?

TIA,
Maciej

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: Owner can't DROP TABLE in DB2 v8 compatibility mode
(in response to Maciej Poraszka)
Maciej,

I don't know for v8, but in v7 you need to be at least DBADM to CREATE a GLOBAL TEMPORARY table. Anyone
can DECLARE a GLOBAL TEMPORARY TABLE.

Walter Trovijo Jr.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: Owner can't DROP TABLE in DB2 v8 compatibility mode
(in response to Walter Trovijo Jr (UOL))
Maciej,

My previous answer was wrong, you don't need to be dbadm to CREATE GLOBAL TEMPORARY table. However, at least in our shop, this method was only used by sysadm to create standard global temporary tables to be used by everyone. Temporary tables created by users or applications for it's own one time use were always defined using DECLARE, which does not have any special security requirements.

Walter.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: Owner can't DROP TABLE in DB2 v8 compatibility mode
(in response to Walter Trovijo Jr (UOL))
Time to open an incident with IBM - We don't use RACF for DB2 auth and I
successfully ran
on both V7 and V8
CREATE GLOBAL TEMPORARY TABLE TESTGLOB LIKE P390I.SYSTABLES;
---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+
COMMIT;
---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
SET CURRENT SQLID='P390I';
---------+---------+---------+---------+---------+---------+---
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---
COMMIT;
---------+---------+---------+---------+---------+---------+---
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---
DROP TABLE TESTGLOB ;
---------+---------+---------+---------+---------+---------+---
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Maciej Poraszka
Sent: Friday, January 12, 2007 5:20 AM
To: [login to unmask email]
Subject: [DB2-L] Owner can't DROP TABLE in DB2 v8 compatibility mode

I have a strange problem in DB2 8 running in compatibility mode.

Subsystem has RACF access control module ([login to unmask email]) VERSION OF PK08127
installed.

When user TST runs following SQLs :

SET CURRENT SQLID='ABC';
COMMIT;
CREATE GLOBAL TEMPORARY TABLE ABC.TESTGLOB LIKE ABC.TEST;

COMMIT;
SET CURRENT SQLID='ABC';
COMMIT;
DROP TABLE ABC.TESTGLOB ;
COMMIT;

he gets :

DROP TABLE ABC.TESTGLOB

SQL ERROR DURING EXECUTE IMMEDIATE

DSNT408I SQLCODE = -551, ERROR: ABC DOES NOT HAVE THE PRIVILEGE TO PERFORM
OPERATION DROP TABLE ON OBJECT
ABC.TESTGLOB

DSNT418I SQLSTATE = 42501 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION


and accompanying RACF message :

ICH408I USER(TST ) GROUP(TESTZ ) NAME(xxx )
DBPX.DSNDB06.DBADM CL(DSNADM )
INSUFFICIENT ACCESS AUTHORITY
FROM DBPX.*.DBADM (G)
ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )

Why ?

Isn't ABC an owner of a table TESTGLOB ?
And as an owner he should have rights of dropping it ...
ABC is a RACF group and user TST submitting a job is a member of this group.


I get the same problem ( -551 ) when I try to DROP ALIAS. No problems with
dropping regular tables.

Above problems don't exist in DB2 v8 subsytem running in new-function mode
with the same level of RACF exit.
In DB2 v 7 also everything works fine as expected. RACF definitions are all
the same in all subsytems.

Any suggestions ?

TIA,
Maciej

----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ulrich Boche

Re: Owner can't DROP TABLE in DB2 v8 compatibility mode
(in response to Mike Bell)
On Friday, 12.01.2007 at 08:57 CST, Mike Bell <[login to unmask email]>
wrote:
> Time to open an incident with IBM - We don't use RACF for DB2 auth and I
> successfully ran
> on both V7 and V8
> CREATE GLOBAL TEMPORARY TABLE TESTGLOB LIKE P390I.SYSTABLES;
> ...snip...

No haste ... there are a few access types that don't work when you use RACF
for DB2 and this might be one of them. Basically, to make an access
decision, RACF needs a user security environment - a control block named
ACEE - for the user or group involved. In DB2, usually the only user
security environment available to RACF is the ACEE of the current user.
Some access decisions can be made based on ownership. Access decisions
involving a user or group that isn't the current user or one of the groups
this user is connected to can be a problem.

I haven't had the time to check the manuals for whether this type of access
should work with RACF, so I can't tell.
--
Ulrich Boche
SVA GmbH, Germany
IBM Premier Business Partner

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Alex Andrade

Re: Owner can't DROP TABLE in DB2 v8 compatibility mode
(in response to Ulrich Boche)
(According to DB2 RACF Guide)To use the CREATETMTAB DB2 keyword in DB2 the
process id "TST" needs
authority to one of the following if RACF is employed

CREATETMTAB (DB2 Auth)
The user must have sufficient authority to:
One of these RACF profile: in this RACF CLass

DB2-subsystem.CREATETMTAB Class MDSNSM or GDSNSM
DB2-subsystem.CREATETAB Class MDSNDB or GDSNDB
DB2-subsystem.SYSCTRL Class DSNADM
DB2-subsystem.SYSADM Class DSNADM


....... if you are allowed to at your site then issue racf command to seach
for profile :

SR CLASS(<class name>) nomask ==> list profiles in a given class

RL <class name> <profile name> AUTH ===> to list auths for a given profile

Check that "TST" has auth explicitly or via a Racf Group.

Have fun !


On Fri, 12 Jan 2007 12:19:50 +0100, Maciej Poraszka <[login to unmask email]>
wrote:

>I have a strange problem in DB2 8 running in compatibility mode.
>
>Subsystem has RACF access control module ([login to unmask email]) VERSION OF PK08127
>installed.
>
>When user TST runs following SQLs :
>
>SET CURRENT SQLID='ABC';
>COMMIT;
>CREATE GLOBAL TEMPORARY TABLE ABC.TESTGLOB LIKE ABC.TEST;
>
>COMMIT;
>SET CURRENT SQLID='ABC';
>COMMIT;
>DROP TABLE ABC.TESTGLOB;
>COMMIT;
>
>he gets :
>
> DROP TABLE ABC.TESTGLOB
>
> SQL ERROR DURING EXECUTE
>IMMEDIATE
>DSNT408I SQLCODE = -551, ERROR: ABC DOES NOT HAVE THE PRIVILEGE TO PERFORM
>OPERATION DROP TABLE ON OBJECT
> ABC.TESTGLOB
>
>DSNT418I SQLSTATE = 42501 SQLSTATE RETURN
>CODE
>DSNT415I SQLERRP = DSNXODD2 SQL PROCEDURE DETECTING
>ERROR
>DSNT416I SQLERRD = 50 0 0 -1 0 0 SQL DIAGNOSTIC
>INFORMATION
>DSNT416I SQLERRD = X'00000032' X'00000000' X'00000000' X'FFFFFFFF'
>X'00000000' X'00000000' SQL DIAGNOSTIC
> INFORMATION
>
>
>and accompanying RACF message :
>
>ICH408I USER(TST ) GROUP(TESTZ ) NAME(xxx )
> DBPX.DSNDB06.DBADM CL(DSNADM )
> INSUFFICIENT ACCESS AUTHORITY
> FROM DBPX.*.DBADM (G)
> ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )
>
>Why ?
>
>Isn't ABC an owner of a table TESTGLOB ?
>And as an owner he should have rights of dropping it ...
>ABC is a RACF group and user TST submitting a job is a member of this
group.
>
>I get the same problem ( -551 ) when I try to DROP ALIAS. No problems with
>dropping regular tables.
>
>Above problems don't exist in DB2 v8 subsytem running in new-function mode
>with the same level of RACF exit.
>In DB2 v 7 also everything works fine as expected. RACF definitions are all
>the same in all subsytems.
>
>Any suggestions ?
>
>TIA,
>Maciej
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Maciej Poraszka

Re: Owner can't DROP TABLE in DB2 v8 compatibility mode
(in response to Alex Andrade)
OK, Alex but CREATE works fine for user TST.
Problem is with DROP .

Maciej

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm