What Global Temporary Table creation controls are there in ACF2

Document ID : KB000098708
Last Modified Date : 04/10/2018
Show Technical Document Details
Introduction:
 
 
Question:
Is there a specific type that can be used in ACF2 for DB2 to control Global Temporary Table creation?
Answer:
 ACF2/DB2 does the same checking that IBM documents on Global Temporary Tables. In native DB2 you can grant ‘all’ to a user against a global temporary table. When a user does ‘alter, delete, insert, select’ on that table, it comes in under type TBL. 

CREATE GLOBAL TEMPORARY TABLE 

The CREATE GLOBAL TEMPORARY TABLE statement creates a description of a temporary table at the current server. Invocation 
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. 

Authorization 
The privilege set that is defined below must include at least one of the following: 
The CREATETMTAB system privilege 
The CREATETAB database privilege for any database 
DBADM, DBCTRL, or DBMAINT authority for any database 
SYSADM or SYSCTRL authority 
System DBADM 


However, DBADM, DBCTRL, or DBMAINT authority is not sufficient authority if you are creating a temporary table for someone else and the table qualifier is not your authorization ID. 

Additional privileges might be required when the data type of a column is a distinct type or the LIKE clause is specified. See the description of distinct-type and LIKE for the details. 

Privilege set: The privilege set is the same as the privilege set for the CREATE TABLE statement. See information about CREATE TABLE Authorization for details. 
Additional Information:

Note: The above protection is for Create Global Temporary Table.
There is no protection for DECLARE Global Temporary Table within ACF2 for DB2.
Per IBM Documentation:

Authorization

None are required, unless the LIKE clause or AS fullselect is specified. In those cases, additional privileges might be required.

PUBLIC implicitly has the following privileges without GRANT authority for declared temporary tables:

  • The CREATETAB privilege to define a declared temporary table in the database that is defined AS WORKFILE, which is the database for declared temporary tables.
  • The USE privilege to use the table spaces in the database that is defined as WORKFILE.
  • All table privileges on the table and authority to drop the table. (Table privileges for a declared temporary table cannot be granted or revoked.)

These implicit privileges are not recorded in the DB2® catalog and cannot be revoked