Can't create an alias on a view (DB2 z/os v11)

Art McEwen

Can't create an alias on a view (DB2 z/os v11)
So I can create an alias on tables but not on views under the same schema, what am I missing?

Explicit grants below make no difference but I left them there to eliminate that possibility.


SET CURRENT SQLID='schema1';
---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, S
---------+---------+---------+---------+------

CREATE VIEW schema1.ON_VIEW
AS
SELECT *
FROM schema1.base_table
WHERE ...
;

---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------

GRANT SELECT ON TABLE schema1.ON_VIEW TO schema2;
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------
GRANT SELECT ON TABLE schema1.ON_VIEW TO DBA1
WITH GRANT OPTION;
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------
SET CURRENT SQLID='DBA1';
---------+---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+--
CREATE ALIAS schema2.BASE_TABLE FOR schema1.base_table;
---------+---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+--
CREATE ALIAS schema2.ON_VIEW FOR schema1.ON_VIEW;
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -551, ERROR: DBA1 DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE ALIAS FOR USER schema2 ON OBJECT
Schema2.ON_VIEW



Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903

Bill Gallagher

Can't create an alias on a view (DB2 z/os v11)
(in response to Art McEwen)
Views are always screwy when it comes to privileges.

To create an alias, the privilege set must include at least one of the listed
authorities or privileges:

To create an alias for a table or a view:

- The CREATEALIAS privilege
- SYSADM or SYSCTRL authority
- DBADM or DBCTRL authority on the database that contains the table, if the
alias is for a table and the value of field DBADM CREATE AUTH on installation
panel DSNTIPP is YES
- System DBADM

Most likely explanation is that your privilege set is satisfield for creating the alias on the table, but not on the view. Is the database for the view (from SYSIBM.SYSTABLES) the same as the database for the base table? If not, it's most likely the DBADM authority that's the culprit.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: McEwen, Art (MOHLTC) <[login to unmask email]>
Sent: Wednesday, September 5, 2018 11:28 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - Can't create an alias on a view (DB2 z/os v11)

So I can create an alias on tables but not on views under the same schema, what am I missing?

Explicit grants below make no difference but I left them there to eliminate that possibility.


SET CURRENT SQLID='schema1';
---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, S
---------+---------+---------+---------+------

CREATE VIEW schema1.ON_VIEW
AS
SELECT *
FROM schema1.base_table
WHERE ...
;

---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------

GRANT SELECT ON TABLE schema1.ON_VIEW TO schema2;
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------
GRANT SELECT ON TABLE schema1.ON_VIEW TO DBA1
WITH GRANT OPTION;
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------
SET CURRENT SQLID='DBA1';
---------+---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+--
CREATE ALIAS schema2.BASE_TABLE FOR schema1.base_table;
---------+---------+---------+---------+---------+---------+---------+--
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+--
CREATE ALIAS schema2.ON_VIEW FOR schema1.ON_VIEW;
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -551, ERROR: DBA1 DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE ALIAS FOR USER schema2 ON OBJECT
Schema2.ON_VIEW



Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903


-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Art McEwen

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Bill Gallagher)

Thanks Bill,

>Most likely explanation is that your privilege set is satisfield for creating the alias on the table, but not on the view. Is >the database for the view (from SYSIBM.SYSTABLES) the same as the database for the base table? If not, it's most >likely the DBADM authority that's the culprit.

Yes the view is on a single table with no joins (just refining the criteria) so the database is the same and the problem seems to span databases.  I can create alias on the base table and have DBADM as well as createalias.    Do i need createalias on sysviewdep or systables to resolve the view during the create?

Joe Geller

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Art McEwen)

I've run into this a few years ago.  I don't remember the details, but System DBADM should be the simplest solution.

Joe

In Reply to Art McEwen:

Thanks Bill,

>Most likely explanation is that your privilege set is satisfield for creating the alias on the table, but not on the view. Is >the database for the view (from SYSIBM.SYSTABLES) the same as the database for the base table? If not, it's most >likely the DBADM authority that's the culprit.

Yes the view is on a single table with no joins (just refining the criteria) so the database is the same and the problem seems to span databases.  I can create alias on the base table and have DBADM as well as createalias.    Do i need createalias on sysviewdep or systables to resolve the view during the create?

Ludovic Janssens

Can't create an alias on a view (DB2 z/os v11)
(in response to Joe Geller)
Hey Joe and Bill,

If I remember well you should grant read with grant option to the schema owner of the alias view. It was either to the view or its base table. I can unfortunately not check.

It is somehow logical as the owner has to be able to pass the authority.

Ludovic Janssens

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


AE-Disclaimer:

Dit bericht is vertrouwelijk en exclusief bestemd voor de geadresseerde ervan. Behoudens andersluidende expliciete afspraak is publicatie, gebruik of verspreiding ervan niet toegelaten. Gelieve de afzender onmiddellijk te verwittigen in geval van foutieve adressering of ontvangst en verwijder het bericht. Visies en opinies uitgedrukt in dit bericht zijn persoonlijk van de afzender tenzij aangegeven als zijnde van AE of verbonden ondernemingen.

This communication is intended for the addressee only and is privileged and confidential. Any unauthorized disclosure, copying, distribution or use is prohibited. If you have received this communication in error, please notify the sender immediately and destroy the original communication. Views and opinions are those of the sender unless clearly stated as being those of AE NV, its subsidiaries and/or associated companies.

Art McEwen

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Ludovic Janssens)

Nope, tried that.  Also tried select with grant option on the account creating the alias.   Still -551 if the object of the alias is a view.

In Reply to Ludovic Janssens:

Hey Joe and Bill,

If I remember well you should grant read with grant option to the schema owner of the alias view. It was either to the view or its base table. I can unfortunately not check.

It is somehow logical as the owner has to be able to pass the authority.

Ludovic Janssens

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


AE-Disclaimer:

Dit bericht is vertrouwelijk en exclusief bestemd voor de geadresseerde ervan. Behoudens andersluidende expliciete afspraak is publicatie, gebruik of verspreiding ervan niet toegelaten. Gelieve de afzender onmiddellijk te verwittigen in geval van foutieve adressering of ontvangst en verwijder het bericht. Visies en opinies uitgedrukt in dit bericht zijn persoonlijk van de afzender tenzij aangegeven als zijnde van AE of verbonden ondernemingen.

This communication is intended for the addressee only and is privileged and confidential. Any unauthorized disclosure, copying, distribution or use is prohibited. If you have received this communication in error, please notify the sender immediately and destroy the original communication. Views and opinions are those of the sender unless clearly stated as being those of AE NV, its subsidiaries and/or associated companies.

Art McEwen

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Art McEwen)

Update from IBM support, basically it's a bug IMO.   DBADM on the underlying db(s) isn't good enough to create aliases on views because there is no dbname in systables for a view.

From their support:

If you're confused why creating the same alias against the base table  

works, it's because for views, there is no database associated with    

views. Therefore we need one of those privileges in the list for       

dealing with implicit database for views.                              

                                                                        

So for Views, the user id needs SYSADM or SYSCTRL.  Also, SYSTEM DBADM would also work, which the doc is missing. We've already contacted the 

doc change team to have the doc updated.                               

                                                                        

Jack Campbell

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Art McEwen)

Art,

I have run into this issue a few times, you need to I developed a work around to separate OWNER and SCHEMA to allow the create ALIAS and GRANT's without having SYSADM auth

 

How to Create view (when not SYSADM1)

Only the OWNER of a view (or SYSADM1), can grant access to a view – however our current method of creating views means the OWNER = SCHEMA for the view,
if these can be separated then we can make  the DBA (DBADMIN) the owner and grant access to the view.

Instead of creating the view as <schema>.VIEW_NAME use the CURRENT SCHEMA and create an unqualified VIEW_NAME, this means the CURRENT SQLID (aka DB2ADMN)
becomes the owner of the object and can grant the access.

Also long as DB2ADMN has SYSCTRL access (check SYSUSERAUTH – for SYSCTRLAUTH=Y), you can drop any views created under the old method

For Example, I was able to create a view in schema = XXD1 and grant access to USER1:

  SET CURRENT SQLID  = 'DBADMIN';       
  SET CURRENT SCHEMA = 'XXD1';       

  CREATE VIEW  VDEPT AS                                                
    (SELECT DEPT_NO , DEPT_NAME , X_UPDT , Z_UPDT FROM DBD1.DEPT )     

  GRANT SELECT ON TABLE XXD1.VDEPT TO USER1;                                                       
  CREATE ALIAS XX.VDEPT FOR XXD1.VDEPT;

 

HTH

Jack

 

 

Art McEwen

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Jack Campbell)

Thanks Jack,
I'm not sure that will work for us, we're trying to prevent the app from having to qualify the schema name when going from environment to environment.


Try this:
Set current SQLID=’DATA’;
Create table tab1... in DB1.TS1;
Create view v1 as select … from tab1.
Grant select on tab1 to APP;
Grant select on v1 to APP;
Create alias APP.tab1 for DATA.tab1;  <-- works
Create alias APP.v1 for DATA.V1;   <-- -551

Jack Campbell

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Art McEwen)

Art,

The issue could be you didn't separate OWNER and SCHEMA - in my example below the owner is the SQLID (DBADMIN)  and the schema is XXD1,  specified using set current schema. All objects are created fully qualified, so the view is created as XXD1.VDEPT, but with owner=DBADMIN  not XXD1. so I can then grant and create alias' on the view.

The common alias 'XX' can be used across all environments, we have 6 test systems and use this regularly.

In your example I did not see SET CURRENT SCHEMA.

  SET CURRENT SQLID  = 'DBADMIN';       
  SET CURRENT SCHEMA = 'XXD1';       

  CREATE VIEW  VDEPT AS                                                
    (SELECT DEPT_NO , DEPT_NAME , X_UPDT , Z_UPDT FROM DBD1.DEPT )     

  GRANT SELECT ON TABLE XXD1.VDEPT TO USER1;                                                       
  CREATE ALIAS XX.VDEPT FOR XXD1.VDEPT;

Art McEwen

RE: Can't create an alias on a view (DB2 z/os v11)
(in response to Jack Campbell)

No difference Paul.   It works (or fails) consistently with what IBM support says.

SET CURRENT SQLID = 'DBA';
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------
SET CURRENT SCHEMA = 'DB2';
---------+---------+---------+---------+---------+---------+------
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+------
SELECT TYPE,DBNAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'DB2'
AND NAME = 'GENERIC_1_CD_TABLE';
---------+---------+---------+---------+---------+---------+------
TYPE DBNAME
---------+---------+---------+---------+---------+---------+------
T GCTMDV01
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

SELECT DBADMAUTH
FROM SYSIBM.SYSDBAUTH
WHERE GRANTEE = 'DBA'
AND NAME = 'PBOLDV01';
---------+---------+---------+---------+---------+---------+---------+------
DBADMAUTH
---------+---------+---------+---------+---------+---------+---------+------
G
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+------
CREATE VIEW COUNTRY_ALIAS_TEST
( COUNTRY_CD , ENGLISH_DESCV_TXT , FRENCH_DESCV_TXT ,
EFFECTIVE_DATE , END_DATE , LAST_UPDT_OPR_CD , LAST_UPDT_TMESTMP
) AS
SELECT SUBSTR ( FIRST_KY_TYPCD , 1 , 3 ) , SUBSTR ( ENGLISH_DESCV_TXT ,
1 , 20 ) , SUBSTR ( FRENCH_DESCV_TXT , 1 , 20 ) , EFFECTIVE_DATE
, END_DATE , LAST_UPDT_OPR_CD , LAST_UPDT_TMESTMP
FROM GENERIC_1_CD_TABLE

WHERE TABLE_NM = 'COUNTRY'
;
---------+---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+----
GRANT SELECT ON TABLE DB2.COUNTRY_ALIAS_TEST TO ALIAS1;
---------+---------+---------+---------+---------+---------+---------+----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+----
CREATE ALIAS ALIAS1.COUNTRY_ALIAS_TEST FOR DB2.COUNTRY_ALIAS_TEST;
---------+---------+---------+---------+---------+---------+---------+----
DSNT408I SQLCODE = -551, ERROR: DBA DOES NOT HAVE THE PRIVILEGE TO
PERFORM OPERATION CREATE ALIAS FOR USER ALIAS1 ON OBJECT
ALIAS1.COUNTRY_ALIAS_TEST