CODEWITHSUNDEEP
db2
FunMatters
FunMatters

Reputation: 601

DB2 Privileges for Create or Declare Global / temp table

Is it possible to grant privileges to allow developers to Create/Declare Temp table/variables but not allow Create Table.

Developers are asked to analyse data from different data sources and a temp table would help a lot.

Upvotes: 1

Views: 1229

Answers (2)

Clockwork-Muse
Clockwork-Muse

Reputation: 13046

Because of how the feature is supposed to be used, no special authorization is required:

Authorization

None are required, unless the LIKE clause is specified when 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.

The exceptions for the LIKE clause basically amount to needing SELECT access to the table/columns, which you'd need anyways...

Upvotes: 2

mustaccio
mustaccio

Reputation: 18945

Since temporary tables require a special type of tablespace, user temporary, I think you should be able to accomplish this indirectly: issue GRANT USE OF TABLESPACE on the user temporary tablespace(s) while revoking that privilege on all regular tablespaces.

Upvotes: 1

Related Questions