Re: Create View Authority ???

Nagesh X Akula

Re: Create View Authority ???
(in response to Roy Boxwell)
I think the key here is CAST function.

Please refer below as per the manual.

Authorization
For every table or view identified in the fullselect, the privilege set that is defined below must include at least one of the following:

The SELECT privilege on the table or view
Ownership of the table or view
DBADM authority for the database (tables only)
SYSADM authority
SYSCTRL authority (catalog tables only)

Additional authorization is required if the definition of the view references any user-defined functions or cast functions that were generated for a distinct type. The privilege set defined below must include the EXECUTE privilege on the referenced functions.



Nagesh Akula
Sr DB2 DBA
Coventry Health Care
804-965-7644 (w)

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Thursday, November 29, 2007 3:53 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Create View Authority ???

Hmm. Don't you have to have SELECT...WITH GRANT AUTHORITY in order to create a view with your own creator, which you would then have authority to GRANT privileges on?

--Phil Sevetson, NYCAPS DBA Support
Financial Information Services Agency of The City of New York
450 West 33rd Street, 4th Floor
New York, NY 10001
phone: (212) 857-1688
mailto: [login to unmask email]
________________________________________
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, November 29, 2007 3:43 PM
To: [login to unmask email]
Subject: [DB2-L] Create View Authority ???

  CREATE VIEW CPR_EQPT AS                                                      
    SELECT CAST(CPRP.EXCH_CODE AS INTEGER) AS EXCH                             
          ,CPRS.BLDG                                                           
          ,CPRP.PROP_ID                                                        
          ,CPRP.MFR_CODE                                                       
          ,CPRP.PART_CODE                                                      
    FROM   CTCPRO.TCPCPR  CPRP                                                 
          ,CTCPRO.TCPSITE CPRS                                                 
    WHERE  CAST(CPRP.EXCH_CODE AS INTEGER) IN                                  
             (0201,0202,9653,9654)                                              
      AND  CAST(CPRP.EXCH_CODE AS INTEGER) =                                   
           CAST(CPRS.EXCH_CODE AS INTEGER)                                     
    ;                                                                           
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -551, ERROR:  TSXVDRI DOES NOT HAVE THE PRIVILEGE TO        
         PERFORM OPERATION CREATE VIEW ON OBJECT CTCPRO.TCPCPR 

The user TSXVDRI has authority to insert, update, select on table CTCPRO.TCPCPR
What am I missing.

Thanks
Dee  
              



=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========



IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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


Email Confidentiality Notice: The information contained in this transmission is confidential, proprietary or privileged and may be subject to protection under the law, including the Health Insurance Portability and Accountability Act (HIPAA).

The message is intended for the sole use of the individual or entity to whom 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 and may subject you to criminal or civil penalties. If you received this transmission in error, please contact the sender immediately by replying to this email and delete the material from any computer.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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

Roy Boxwell

Antwort: [DB2-L] Create View Authority ???




Hi

The problem is that the userid does not have SYSADM. To create a VIEW with
a different creatorid from the process needs a "hefty" userid. Here is a
cut-and-paste from CREATE VIEW

If the statement is dynamically prepared, the following rules apply:

v If the SQL authorization ID of the process has SYSADM authority, the
owner of the view can be any authorization ID. If that authorization ID has
SYSCTRL but not SYSADM authority, the following is true: the owner of the
view can be any authorization ID, provided the view does not refer to user
tables or views in the first FROM clause of its defining fullselect. (It
could refer instead, for example, to catalog tables or views thereof.) If
the view satisfies the rules in the preceding paragraph, and if no errors
are present in the CREATE statement, the view is created, even if the owner
has no privileges at all on the tables and views identified in the view's
fullselect.

v If SQL authorization ID of the process 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 different from the SQL 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.Note: The owner of the view must have the
SELECT privilege on all tables and views in the CREATE VIEW statement, or,
if the owner does not have the SELECT privilege on a table, the creator
must have DBADM authority on the database that contains that table.

v If the SQL authorization ID of the process lacks SYSADM, SYSCTRL, or
DBADM authority, or if the SQL authorization ID of the process fails to
meet any of the previous conditions, only the authorization IDs of the
process can own the view. In this case, the privilege set is the privileges
that are held by the authorization ID selected for ownership.

HTH!
Roy

Mike Bell

Re: Create View Authority ???
(in response to Nagesh X Akula)
thoughts
1. It doesn't require DBADM or SYSADM to create a view unless you try to
qualify the view with a different creator.
That would require userid.CPR_EQPT in the create.
2. CAST is an implicit function - doesn't need any authorization.
3. There are 2 tables referenced CTCPRO.TCPCPR and CTCPRO.TCPSITE -
Does the user have select auth on both tables?

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Dee Reins
Sent: Thursday, November 29, 2007 2:43 PM
To: [login to unmask email]
Subject: [DB2-L] Create View Authority ???

CREATE VIEW CPR_EQPT AS


SELECT CAST(CPRP.EXCH_CODE AS INTEGER) AS EXCH


,CPRS.BLDG


,CPRP.PROP_ID


,CPRP.MFR_CODE


,CPRP.PART_CODE


FROM CTCPRO.TCPCPR CPRP


,CTCPRO.TCPSITE CPRS


WHERE CAST(CPRP.EXCH_CODE AS INTEGER) IN


(0201,0202,9653,9654)


AND CAST(CPRP.EXCH_CODE AS INTEGER) =


CAST(CPRS.EXCH_CODE AS INTEGER)


;


---------+---------+---------+---------+---------+---------+---------+------
---+

DSNT408I SQLCODE = -551, ERROR: TSXVDRI DOES NOT HAVE THE PRIVILEGE TO


PERFORM OPERATION CREATE VIEW ON OBJECT CTCPRO.TCPCPR



The user TSXVDRI has authority to insert, update, select on table
CTCPRO.TCPCPR

What am I missing.



Thanks

Dee





IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the overall
user experience of DB2-L. These changes are coming on November 30th. Details
at http://www.idug.org

----------------------------------------------------------------------------
----- 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 IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the overall
user experience of DB2-L. These changes are coming on November 30th. Details
at http://www.idug.org

----------------------------------------------------------------------------
----- 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 IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the overall
user experience of DB2-L. These changes are coming on November 30th. Details
at http://www.idug.org

----------------------------------------------------------------------------
----- 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 IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the overall
user experience of DB2-L. These changes are coming on November 30th. Details
at http://www.idug.org

----------------------------------------------------------------------------
----- 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
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.9/1157 - Release Date: 11/28/2007
12:29 PM


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.9/1157 - Release Date: 11/28/2007
12:29 PM


IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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

Peter Vanroose

Re: Create View Authority ???
(in response to Mike Bell)
> The user TSXVDRI has authority to insert, update, select on table
> CTCPRO.TCPCPR
>
> What am I missing.

Most likely, the user's secundary ID has this SELECT auth.
For DML (select etc.) the secundary ID is automatically consulted, but
not for DDL.
Since CREATE VIEW is DDL, this could be the explanation.

Solution:
- SET CURRENT SQLID = ....
- CREATE VIEW ...


-- Peter Vanroose
ABIS Training & Consulting


















__________________________________________________________
Ta semester! - sök efter resor hos Yahoo! Shopping.
Jämför pris på flygbiljetter och hotellrum här:
http://shopping.yahoo.se/c-169901-resor-biljetter.html?partnerId=96914052

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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

David Seibert

Re: Create View Authority ???
(in response to Peter Vanroose)
And once you get past this, you'll run into the gotcha that when you
create a view with an owner other than yourself, you can't Grant
privileges on that view unless you have SYSADM. I think it's also true
you can't drop the view, but I haven't run into that.

(please excuse if this is a duplicate -- the listserv told me it was
rejected)

Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Peter Vanroose
Sent: Friday, November 30, 2007 2:32 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Create View Authority ???

> The user TSXVDRI has authority to insert, update, select on table
> CTCPRO.TCPCPR
>
> What am I missing.

Most likely, the user's secundary ID has this SELECT auth.
For DML (select etc.) the secundary ID is automatically consulted, but
not for DDL.
Since CREATE VIEW is DDL, this could be the explanation.

Solution:
- SET CURRENT SQLID = ....
- CREATE VIEW ...


-- Peter Vanroose
ABIS Training & Consulting

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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

Peter Vanroose

Re: Create View Authority ???
(in response to David Seibert)
> And once you get past this, you'll run into the gotcha that when you
> create a view with an owner other than yourself, you can't Grant
> privileges on that view unless you have SYSADM. I think it's also
> true you can't drop the view, but I haven't run into that.

Well, you can, but only after setting your CURRENT SQLID to the view's
creator.
And if this is a group id, others within the same group can do it for
you, i.e., inadvertently drop "your" view.

-- Peter Vanroose
ABIS Training & Computing


















___________________________________________________
Sök efter kärleken!
Hitta din tvillingsjäl på Yahoo! Dejting: http://se.meetic.yahoo.net

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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

Lockwood Lyon

Re: Create View Authority ???
(in response to Peter Vanroose)
Another factor is the setting of your DBACRVW ZParm value, which will
affect whether IDs with DBADM/DBCTRL can create views for another
auth-ID (although this 'still' does not allow granting authority on that
view).


Lock Lyon
Fifth Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Peter Vanroose
Sent: Friday, November 30, 2007 5:47 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Create View Authority ???

> And once you get past this, you'll run into the gotcha that when you
> create a view with an owner other than yourself, you can't Grant
> privileges on that view unless you have SYSADM. I think it's also
> true you can't drop the view, but I haven't run into that.

Well, you can, but only after setting your CURRENT SQLID to the view's
creator.
And if this is a group id, others within the same group can do it for
you, i.e., inadvertently drop "your" view.

-- Peter Vanroose
ABIS Training & Computing

This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
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: Create View Authority ???
(in response to Lockwood Lyon)

NOT TRUE - you do NOT have to set the SQLID to the correct group ID to drop
anything. DB2 does not have to record anything in the catalog for a DROP so
if the userid has the authority anywhere in a secondary id, DB2 will treat
DROP just like SELECT, INSERT, UPDATE, and DELETE. ALL of these will work
without setting the current SQLID to the correct group id.

Been there - done that and recovery was not fun.

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Peter Vanroose
Sent: Friday, November 30, 2007 5:47 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Create View Authority ???

> And once you get past this, you'll run into the gotcha that when you
> create a view with an owner other than yourself, you can't Grant
> privileges on that view unless you have SYSADM. I think it's also
> true you can't drop the view, but I haven't run into that.

Well, you can, but only after setting your CURRENT SQLID to the view's
creator.
And if this is a group id, others within the same group can do it for
you, i.e., inadvertently drop "your" view.

-- Peter Vanroose
ABIS Training & Computing

This e-mail transmission contains information that is confidential and may
be privileged. It is intended only for the addressee(s) named above. If
you receive this e-mail in error, please do not read, copy or disseminate it
in any manner. If you are not the intended recipient, any disclosure,
copying, distribution or use of the contents of this information is
prohibited. Please reply to the message immediately by informing the sender
that the message was misdirected. After replying, please erase it from your
computer system. Your assistance in correcting this error is appreciated.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the overall
user experience of DB2-L. These changes are coming on November 30th.
Details at http://www.idug.org

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

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.13/1164 - Release Date: 12/2/2007
11:30 AM


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.13/1167 - Release Date: 12/3/2007
12:20 PM


IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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

Dee Reins

Re: Create View Authority ???
(in response to Mike Bell)


Thank you all for your help. I believe the authority opportunity was
discovered by Phil Stevenson. Thank you Phil.

His message gave me the clue I needed.

Hmm. Don't you have to have SELECT...WITH GRANT AUTHORITY in order to
create a view with your own creator,

which you would then have authority to GRANT privileges on?



This is a test system, so this is what I did to make the view creation
work.



GRANT SELECT ON TABLE CTCPRO.TCPSITE TO PUBLIC;



GRANT SELECT ON TABLE CTCPRO.TCPCPR TO PUBLIC;



I think that using SELECT ... WITH GRANT AUTHORITY would have worked
just as well.



Thanks again for all the help



Mr. Dee Reins.




IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

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