Creating and/or Dropping Views

Document ID : KB000113650
Last Modified Date : 12/09/2018
Show Technical Document Details
Question:
Testing DB2 V12 and receiving this error. What access is needed?
CADB2036 - A null field was passed for the resource name
Trace showing DSNT408I SQLCODE = -164, ERROR: xxx DOES NOT HAVE THE PRIVILEGE TO CREATE A VIEW xxxxx
 
Answer:
1. Creating and/or Dropping Views                                          
    Authorizing the  creation of  a view in  CA-Top Secret/DB2 is a        
   special process.   Like DB2,  CA-Top Secret/DB2 requires that the       
   creator of the view have the SELECT authority on the base tables or     
   views.  Unlike DB2, however,  CA-Top Secret/DB2 does  not enforce       
   that the  qualifier of the view is one of the  creator's process        
   IDs.   Instead,  CA-Top Secret/DB2 checks a CA-Top Secret/DB2           
   special privilege called CREATE.   CREATE is specified as DB2TABLE      
   access level for the  view,  and is checked when a user creates  or     
   drops  a view.    If the  PERMIT to  the view  does not include         
   drops  a view.    If the  PERMIT to  the view  does not include 
   the   CREATE  access   level  to   create  or   drop  a   view, 
   CA-Top Secret/DB2 denies the request.          

DB2DBASE with acc(DBADM) Is not enough, you will either      
need CREATE,VIEW to the DB2TABLE resource, or DB2SYS(sysctrl)
or DB2SYS(SYSADM) authority