This discussion is archived
11 Replies Latest reply: May 6, 2013 11:16 PM by 1004521 RSS

User creation

$phinx19 Newbie
Currently Being Moderated
Hi All,

OS: Windows
DB:11.2.0.1 xpress edition

Here is the thing:

I have created a user using the below command:
create user xyz identified by xyz default tablespace users temporary tablespace temp;
Now, I just grant him Connect Role and the privilege Create table as:
grant connect, create table to xyz;
You must have noticed that I have not allocated the said user with any quota whatsoever.

Now, the above command will allow the user xyz to create a session and also allow him to create a table but I am getting the below error.
SQL> create table mandy (sr_no number, fname varchar2(10));
create table mandy (sr_no number, fname varchar2(10))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
Now when i query, the role Resource using:
SELECT PRIVILEGE
>FROM dba_sys_privs
>WHERE grantee = 'RESOURCE';

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE

and I just to test, I gave all the privileges one by one and see if it allows to create a table (which is a stupid move,as from the privileges one can at prima facie that it wont help the cause).

And it didn't!

When I simply fire:
grant resource to xyz;
Oracle allows me to create the said table.

It means that it also have the unlimited table priv in resource, Just need to know, why the same is NOT reflected in the output of the dba_sys_privs query?
  • 1. Re: User creation
    EdStevens Guru
    Currently Being Moderated
    $phinx19 wrote:
    Hi All,

    OS: Windows
    DB:11.2.0.1 xpress edition

    Here is the thing:

    I have created a user using the below command:
    create user xyz identified by xyz default tablespace users temporary tablespace temp;
    Now, I just grant him Connect Role and the privilege Create table as:
    grant connect, create table to xyz;
    You must have noticed that I have not allocated the said user with any quota whatsoever.

    Now, the above command will allow the user xyz to create a session and also allow him to create a table but I am getting the below error.
    SQL> create table mandy (sr_no number, fname varchar2(10));
    create table mandy (sr_no number, fname varchar2(10))
    *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'USERS'
    Now when i query, the role Resource using:
    SELECT PRIVILEGE
    FROM dba_sys_privs
    WHERE grantee = 'RESOURCE';
    PRIVILEGE
    ----------------------------------------
    CREATE TRIGGER
    CREATE SEQUENCE
    CREATE TYPE
    CREATE PROCEDURE
    CREATE CLUSTER
    CREATE OPERATOR
    CREATE INDEXTYPE
    CREATE TABLE

    and I just to test, I gave all the privileges one by one and see if it allows to create a table (which is a stupid move,as from the privileges one can at prima facie that it wont help the cause).

    And it didn't!

    When I simply fire:
    grant resource to xyz;
    Oracle allows me to create the said table.

    It means that it also have the unlimited table priv in resource, Just need to know, why the same is NOT reflected in the output of the dba_sys_privs query?
    Because RESOURCE is a role, not a privilege ....
    And it's not one you should give out lightly. Better to have granted said user some quota on said tablespace.

    Edited by: EdStevens on Apr 25, 2013 3:46 PM
  • 2. Re: User creation
    $phinx19 Newbie
    Currently Being Moderated
    Ed,

    I meant, the output of the query:


    SELECT PRIVILEGE
    FROM dba_sys_privs
    WHERE grantee = 'RESOURCE';

    This query should give me all the privileges within the role Resource (which should also include the unlimited quota on tablespace) or is there some other query.

    Hope it is clear this time around
  • 3. Re: User creation
    JohnWatson Guru
    Currently Being Moderated
    Granting the RESOURCE role includes an implicit grant of the UNLIMITED TABLESPACE privilege, you'll see it in dba_sys_privs. This is why you should never grant RESOURCE. The DBA role does the same thing.
  • 4. Re: User creation
    %bala% Explorer
    Currently Being Moderated
    I didnt see the Unlimited tablespace privilege in resource role. Please clarify

    PRIVILEGE
    ----
    CREATE TRIGGER
    CREATE SEQUENCE
    CREATE TYPE
    CREATE PROCEDURE
    CREATE CLUSTER
    CREATE OPERATOR
    CREATE INDEXTYPE
    CREATE TABLE
  • 5. Re: User creation
    JohnWatson Guru
    Currently Being Moderated
    You didn't read what I said (or say "thank you"): granting the RESOURCE role includes an implicit grant of UNLIMITED TABLESPACE which you see if you query dba_sys_privs:
    orcl>
    orcl> create user jw identified by jw;
    
    User created.
    
    orcl> grant resource to jw;
    
    Grant succeeded.
    
    orcl> select * from dba_sys_privs where grantee='JW';
    
    GRANTEE                        PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    JW                             UNLIMITED TABLESPACE                     NO
    
    orcl>
  • 6. Re: User creation
    %bala% Explorer
    Currently Being Moderated
    good to know new thing, thanks John
  • 7. Re: User creation
    $phinx19 Newbie
    Currently Being Moderated
    Thanks
  • 8. Re: User creation
    1006363 Newbie
    Currently Being Moderated
    Hi,

    So if no default tablespace has mentioned for user and we are granting resource role, will the unlimited tablespace be on SYSTEM by default?

    Regards,
    Chirag
  • 9. Re: User creation
    %bala% Explorer
    Currently Being Moderated
    That would be the default tablespace of database.
    You can check the default tablespace from database_properties view.

    To check database properties, for example: default tablespace type, various nls parameters, timezone etc run the following query:
    SELECT * FROM DATABASE_PROPERTIES;
  • 10. Re: User creation
    1006363 Newbie
    Currently Being Moderated
    Thank you Bala.
  • 11. Re: User creation
    1004521 Newbie
    Currently Being Moderated
    what bala said is correct , we ll get to know the default tablespace by giving above qeury,
    As per my concern For any user the default tablespace will be USERS.
    No users should be given Qouta on SYSTEM tablespace;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points