Grant “CREATE TEMPORARY TABLE” privilege
Posted by Kamran Agayev A. on 20th June 2012
I was asked to grant a privilege for a user to create only temporary tables. I was sure there’s no the specified grant such as
[php]
SQL>GRANT CREATE TEMPORARY TABLE to user1;
[/php]
After making a simple research, I saw the following thread where Justin Cave mentions that “However, if you grant the user the CREATE TABLE privilege but do not grant the user quota on any permanent tablespace, they would be unable to create permanent tables but should be able to create global temporary tables”
https://forums.oracle.com/forums/thread.jspa?threadID=2197364
A simple test proved the above quote:
[php]
SQL> create user user1 identified by user1;
User created.
SQL> grant connect to user1;
Grant succeeded.
SQL> grant create table to user1;
Grant succeeded.
SQL> conn user1/user1
Connected.
SQL> create table a (id number);
create table a (id number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> create global temporary table a (id number);
Table created.
SQL>
[/php]
Posted in Administration | 5 Comments »