11 Replies Latest reply: May 7, 2013 1:16 AM by 1004521 RSS

    User creation

    $phinx19
      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
          $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
            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
              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%
                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
                  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%
                    good to know new thing, thanks John
                    • 7. Re: User creation
                      $phinx19
                      Thanks
                      • 8. Re: User creation
                        1006363
                        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%
                          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
                            Thank you Bala.
                            • 11. Re: User creation
                              1004521
                              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;)