Global temporary table and system priviliges

[blom0344]

I'm trying to build a rather complex script to perform a scan on datadictionary tables.
The purpose of the scan is to check whether certain tables are present and which ones contain no data.
To achieve I store a reference set in a GTT.

GTT's are stored in the temp tablespace. I wonder if I need to have create table priviliges to run the scan. Are GTT's the exeption to the rule?
Normal access is done by consultants that have rather limited priviliges..

Ties Blom

 

[SantaMufasa]

Ties,

Is the user with which you are trying to achieve this objective a member of the "CONNECT" role? If so, the "CONNECT" role possesses the "CREATE TABLE" privilege, which is sufficient to successfully "CREATE GLOBAL TEMPORARY TABLE..."

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[blom0344]

Santa,

Thanks, I was kind of hoping that even without explicit 'create table' priviliges it would be possible to run a script that uses a GTT. (bit naive I guess..)

Ties Blom

 

[Dagon]

I may be missing the point here, but why would you need "create table" privilege unless you're actually creating the temporary table in each run ? Can't you just create it once and then grant privileges to the different users so that they can insert data into it ?

 

[blom0344]

The GTT is only used to have a complete set of some 320 tables as s reference-set. This set is compared with data from the catalogue views to scan if tables are missing or empty. The average consultant can execute an SQL , but is not capable (or enabled) to create and drop objects in the database. By using a GTT he does not even need to know what is going on in the scan.
(And after the session no traces are left)

Ties Blom

 

[Dagon]

I can see that, but I'm still puzzled why you think the consultants would need "create table" privilege just to insert data into the temporary table.

 

[blom0344]

The GTT is created in the scan-script as executed by the consultant. To rephrase my original question: Does one need create table privilige to execute the script that contains creation at runtime of the GTT?

Ties Blom

 

[Dagon]

Why do need to keep re-creating the temporary table ? Surely the whole point of a GTT is that lots of users can write to it and they only see their own temporary data, so you only need to create the table once and get all users to point to that single copy.

 

[blom0344]

The GTT is only needed during the session where the scan is performed. For each customer-site this is a one-time occasion. The GTT only contains a hardcoded set of data as a reference set for the scan..

Ties Blom