DBADM cannot create view within view

Sally Mir

DBADM cannot create view within view
We have a DBA who is trying to create a view that selects data from another view, neither of which (of course) is owned by her id. It is failing with a -164. She can CREATE the inner view, and also SELECT from it.

We have Googled to try to find out the correct authorizations she would need in order to get this to work, but seem to have come up short. With all the changes to how authorizations work in the latest releases of Db2 (we're on V11), it is difficult to understand how they all fit together.

Has anyone out there run into this problem?

Thanks in advance,

Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
Winston-Salem, NC 27106
336.733.0946




The information in this transmission may contain proprietary and non-public information of BB&T or its affiliates and may be subject to protection under the law. The message is intended for the sole use of the individual or entity to which it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited. If you received this message in error, please delete the material from your system without reading the content and notify the sender immediately of the inadvertent transmission.

Larry Jardine

DBADM cannot create view within view
(in response to Sally Mir)
Grant SELECT on the inner view to the id being used to create the outer view?

Larry Jardine
Aetna

From: Mir, Sally [mailto:[login to unmask email]
Sent: Tuesday, February 13, 2018 1:46 PM
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - DBADM cannot create view within view

**** External Email - Use Caution ****
We have a DBA who is trying to create a view that selects data from another view, neither of which (of course) is owned by her id. It is failing with a -164. She can CREATE the inner view, and also SELECT from it.

We have Googled to try to find out the correct authorizations she would need in order to get this to work, but seem to have come up short. With all the changes to how authorizations work in the latest releases of Db2 (we're on V11), it is difficult to understand how they all fit together.

Has anyone out there run into this problem?

Thanks in advance,

Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
Winston-Salem, NC 27106
336.733.0946




The information in this transmission may contain proprietary and non-public information of BB&T or its affiliates and may be subject to protection under the law. The message is intended for the sole use of the individual or entity to which it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited. If you received this message in error, please delete the material from your system without reading the content and notify the sender immediately of the inadvertent transmission.
-----End Original Message-----

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

Bill Gallagher

DBADM cannot create view within view
(in response to Sally Mir)
I suspect that this may be the issue (from the description of -164 SQLCODE):

The DBADM privilege should be granted on any of the databases that contain at least one of the tables on which this CREATE
VIEW is based.

SYSIBM.SYSTABLES, column DBNAME will show the database that the view is "assigned" to. Sometimes, especially if you're talking about a view of a view, the DBNAME will be DSNDB06.

That may be what's causing the problem, if she doesn't have DBADM on DSNDB06.

I would think that a SYSADM should be able to create the outer view.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Mir, Sally [mailto:[login to unmask email]
Sent: Tuesday, February 13, 2018 1:46 PM
To: [login to unmask email]
Subject: [DB2-L] - DBADM cannot create view within view

We have a DBA who is trying to create a view that selects data from another view, neither of which (of course) is owned by her id. It is failing with a -164. She can CREATE the inner view, and also SELECT from it.

We have Googled to try to find out the correct authorizations she would need in order to get this to work, but seem to have come up short. With all the changes to how authorizations work in the latest releases of Db2 (we're on V11), it is difficult to understand how they all fit together.

Has anyone out there run into this problem?

Thanks in advance,

Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
Winston-Salem, NC 27106
336.733.0946




The information in this transmission may contain proprietary and non-public information of BB&T or its affiliates and may be subject to protection under the law. The message is intended for the sole use of the individual or entity to which it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited. If you received this message in error, please delete the material from your system without reading the content and notify the sender immediately of the inadvertent transmission.
-----End Original Message-----

Wolfgang Manus

RE: DBADM cannot create view within view
(in response to Sally Mir)

Hi Sally,

if you're not SYSADM or SYSDBA and want to create a view with an owner other than your ID, you need to be part of a secondary authid group named as the new view owner. That's what the -164 is saying, it has nothing to do with the inner view authorizations (that you need in addition). In other words, without SYSADM/SYSDBA, you need to be able to SET CURRENT SQLID to the new view OWNER. The new view owner needs all required privileges of the referenced (inner) tables/views. 

Example (connected as SALLY, not holding SYSDBA/SYSADM):

   SET CURRENT SQLID='NEWOWNER';

   CREATE VIEW NEWOWNER.NEWVIEW AS SELECT * FROM INNER.TABLE;

In this case, 2 things are required:

1. SALLY must be part of a secondary authid group named NEWOWNER.

2. NEWOWNER needs to be able to select from INNER.TABLE. If you want to grant privileges on NEWOWNER.NEWVIEW to others, NEWOWNER also needs GRANT OPTION on INNER.TABLE (if it's not PUBLIC).

That's at least my understanding of how it works... hope it helps...

Best regards - Wolfgang

Kathleen Lisle

RE: DBADM cannot create view within view
(in response to Wolfgang Manus)

I am the DBA who is working with Sally. Many thanks to those of you who have offered suggestions. After following up on your ideas and other ideas from our own DBA, Systems, and Security staff, we have narrowed the issue to this restriction (taken from the DB2 11 SQL Reference, "Create View"):

If the privilege set lacks system DBADM, SYSADM and SYSCTRL but includes

DBADM authority on at least one of the databases that contains a table from

which the view is created, the owner of the view can be any authorization ID if

all of the following conditions are true:

– The value of field DBADM CREATE AUTH was set to YES on panel DSNTIPP during DB2 installation.

– The view is not based only on views.

This is a "view based only on views." At Sally's suggestion, I added a clause to the predicate of the outer view:

AND EXISTS (SELECT 1 FROM SYSIBM.SYSDUMMY1)

Now the outer view includes a table, and -- incredibly -- I can create the view. Unfortunately, this is a vendor view, so I don't want to change the code. I'm still looking for the "right" solution, but at least we've identified the problem.

Walter Janißen

AW: DBADM cannot create view within view
(in response to Kathleen Lisle)
So the question is: Why does this restriction exist? Does it make any sense?
I don’t know, if this restriction still exists in V12

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Kathleen Lisle [mailto:[login to unmask email]
Gesendet: Donnerstag, 15. Februar 2018 14:31
An: [login to unmask email]
Betreff: [DB2-L] - RE: DBADM cannot create view within view


I am the DBA who is working with Sally. Many thanks to those of you who have offered suggestions. After following up on your ideas and other ideas from our own DBA, Systems, and Security staff, we have narrowed the issue to this restriction (taken from the DB2 11 SQL Reference, "Create View"):

If the privilege set lacks system DBADM, SYSADM and SYSCTRL but includes

DBADM authority on at least one of the databases that contains a table from

which the view is created, the owner of the view can be any authorization ID if

all of the following conditions are true:

– The value of field DBADM CREATE AUTH was set to YES on panel DSNTIPP during DB2 installation.

– The view is not based only on views.

This is a "view based only on views." At Sally's suggestion, I added a clause to the predicate of the outer view:

AND EXISTS (SELECT 1 FROM SYSIBM.SYSDUMMY1)

Now the outer view includes a table, and -- incredibly -- I can create the view. Unfortunately, this is a vendor view, so I don't want to change the code. I'm still looking for the "right" solution, but at least we've identified the problem.

-----End Original Message-----
Attachments

  • image001.png (2.6k)