Kamran Agayev's Oracle Blog

Oracle Certified Master

Grant “CREATE TEMPORARY TABLE” privilege

Posted by Kamran Agayev A. on June 20th, 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]

5 Responses to “Grant “CREATE TEMPORARY TABLE” privilege”

  1. syed Says:

    hi,
    i worked as u said..
    its not working for me… i have a screen shot, pls gimme ur mail id i will post it to you.. smileshaff8@gmail.com

  2. Kamran Agayev A. Says:

    You don’t need to send me a screenshot, post output from Sql*Plus

  3. syed Says:

    i dint noticed to grant create table ..its working now!!
    thank u!!

  4. syed Says:

    please explain this using video tutorial..
    how to backup in multipaths using RMAN backup..pls help me out!!i did configure with disk 1 and disk 2 but its not working any other option pls explain…
    i did this
    1. CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
    2.CONFIGURE CHANNEL DEVICE TYPE DISK 1 FORMAT ‘/backup/ora_df%t_s%s_s%p’;
    3.CONFIGURE CHANNEL DEVICE TYPE DISK 2 FORMAT ‘/backup2/ora_df%t_s%s_s%p’;

    4.backup database
    configuring successfully and backup is done successfully but backup will @ once path not in 2nd path..

  5. syed Says:

    and also i want to purchase backup and recovery book, is it available in india? does that book includes of video tutorials?

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>