CODEWITHSUNDEEP
oracle-database
cardycakes
cardycakes

Reputation: 435

Oracle: Can you grant CREATE GLOBAL TEMPORARY TABLE specifically?

Can you grant privilege to allows the creation of a global temporary table ONLY, i.e. without CREATE TABLE?

The background of this stems from security models that don't allow 'CREATE TABLE'... I can't find a reference to any specific privileges.

Upvotes: 4

Views: 9794

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

I can't find a reference to any specific privileges.

Because, as far as I know, there is none. You'll still have to grant CREATE TABLE privilege to a user so he could create TEMPORARY tables.

In order to prevent a user from creating regular tables, and allow him to create only temporary tables you might consider the following:

Grant CREATE TABLE privilege to a user and revoke quota on a specific tablespace.

alter user <<user_name>> quota 0M on <<tablespace>>;

It might be the user's default tablespace or a different one.

In Oracle's versions with no DEFFERED_SEGMENT_CREATION it'll be enough for the user to see space quota exceeded for tablespace when he/she tries to create a regular table.

In Oracle's version with DEFFERED_SEGMENT_CREATION a user will still be able to create regular tables, but will not be able populate them. The second the user tries to execute an INSERT statement the space quota exceeded for tablespace appears.

So you might consider setting deferred_segment_creation parameter to false.

alter system set deferred_segment_creation = false;

Upvotes: 7

Related Questions