9 Replies Latest reply: Jan 1, 2013 11:11 PM by Venkateshj RSS

    Roles Creating and assigning to user

    Venkateshj
      Hi all,


      Am creating a role as venki & granting some privileges to that role i.e, create session,create table...

      Then am creating a user & assigning a role "venki" to the created user.

      then after connecting to the user am unable to create a table in that user, am getting privilege problem..

      Please give a solution..


      Thanks in advance..

      Edited by: Venkateshj on Jan 1, 2013 3:32 PM

      Edited by: Venkateshj on Jan 1, 2013 4:12 PM
        • 1. Re: Roles Creating and assigning to user
          user639304
          Hi,

          What is the error message you get?
          • 2. Re: Roles Creating and assigning to user
            Venkateshj
            user639304 wrote:
            Hi,

            What is the error message you get?
            SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 1 15:57:27 2013

            Copyright (c) 1982, 2005, Oracle. All rights reserved.

            Enter user-name: oe/oe@csdb

            Connected to:
            Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

            SQL> create role venki;

            Role created.

            SQL> grant connect,resource,create table to venki;

            Grant succeeded.

            SQL> create user tpt identified by tpt default tablespace TS1 account unlock;

            User created.

            SQL> grant venki to tpt;

            Grant succeeded.

            SQL> conn tpt/tpt@csdb
            Connected.
            SQL> create table app(sname varchar2(9),sid int);
            create table app(sname varchar2(9),sid int)
            *
            ERROR at line 1:
            ORA-01950: no privileges on tablespace 'TS1'


            SQL>
            • 3. Re: Roles Creating and assigning to user
              Niket Kumar
              connect as sys or system use and fire following query

              sql>alter user oe default tablespace TS1 quota unlimited on TS1;

              above query will provide rigts on tablespace.
              • 4. Re: Roles Creating and assigning to user
                Chanchal Wankhade
                Hi,

                Roles does not provide privileges to the user you need to assign the privileges by logging in as SYSTEM user and follow below command
                ALTER USER xyz QUOTA UNLIMITED ON tablespace_name ;
                • 5. Re: Roles Creating and assigning to user
                  Venkateshj
                  Niket Kumar wrote:
                  connect as sys or system use and fire following query

                  sql>alter user oe default tablespace TS1 quota unlimited on TS1;

                  above query will provide rigts on tablespace.
                  ya while firing above query am able to create table on tpt user..

                  am giving default tablespace to user tpt so that am allocating storage memory for that user..

                  then am granting create table to tpt user..

                  again why should i assign quota to unlimited..

                  can u please explain in a brief manner...
                  • 6. Re: Roles Creating and assigning to user
                    Niket Kumar
                    if you give only default table space it means whenever the object's are created by this user will be created in that default tablespace.quota must be provided on that tablespace to create objects.quota is the size provided to user to create objects in limits on size. this is helpful when 50 or 100 users have same tablespace and you give 100 MB to everyone so that no other user can create object more them 100MB and spzce is also available for other users.
                    • 7. Re: Roles Creating and assigning to user
                      Venkateshj
                      Niket Kumar wrote:
                      if you give only default table space it means whenever the object's are created by this user will be created in that default tablespace.quota must be provided on that tablespace to create objects.quota is the size provided to user to create objects in limits on size. this is helpful when 50 or 100 users have same tablespace and you give 100 MB to everyone so that no other user can create object more them 100MB and spzce is also available for other users.
                      Thank u...
                      • 8. Re: Roles Creating and assigning to user
                        JohnWatson
                        The answer you have so far is not necessarily correct. You have granted RESOURCE to the role, and RESOURCE includes an implicit grant of UNLIMITED TABLESPACE. See this:
                        orcl> create role venki;
                        
                        Role created.
                        
                        orcl> grant connect,resource,create table to venki;
                        
                        Grant succeeded.
                        
                        orcl> create user tpt identified by tpt default tablespace users account unlock;
                        
                        User created.
                        
                        orcl> grant venki to tpt;
                        
                        Grant succeeded.
                        
                        orcl> conn tpt/tpt
                        Connected.
                        
                        Session altered.
                        
                        orcl> create table app(sname varchar2(9),sid int);
                        
                        Table created.
                        
                        orcl>
                        there is no need to grant quota on a tablespace if the user already has RESOURCE, which is one reason why you should never grant RESOURCE to anyone. Clearly, there is sonething else going on in your database. Another indication that things are not as they seem is that in 11.2.x by default you do not need quota on a tablespace (or unlimnited tablespace) to create tables, because by default segment creation is deferred. So you get the error only when you try to insert into the table:
                        orcl> create user me identified by me;
                        
                        User created.
                        
                        orcl> grant create session,create table to me;
                        
                        Grant succeeded.
                        
                        orcl> conn me/me
                        Connected.
                        
                        Session altered.
                        
                        orcl> create table t1(c1 date) tablespace system;
                        
                        Table created.
                        
                        orcl> insert into t1 values(sysdate);
                        insert into t1 values(sysdate)
                                    *
                        ERROR at line 1:
                        ORA-01950: no privileges on tablespace 'SYSTEM'
                        
                        
                        orcl>
                        I think you need to investigate further. Things are not as they seem.
                        • 9. Re: Roles Creating and assigning to user
                          Venkateshj
                          JohnWatson wrote:
                          The answer you have so far is not necessarily correct. You have granted RESOURCE to the role, and RESOURCE includes an implicit grant of UNLIMITED TABLESPACE. See this:
                          orcl> create role venki;
                          
                          Role created.
                          
                          orcl> grant connect,resource,create table to venki;
                          
                          Grant succeeded.
                          
                          orcl> create user tpt identified by tpt default tablespace users account unlock;
                          
                          User created.
                          
                          orcl> grant venki to tpt;
                          
                          Grant succeeded.
                          
                          orcl> conn tpt/tpt
                          Connected.
                          
                          Session altered.
                          
                          orcl> create table app(sname varchar2(9),sid int);
                          
                          Table created.
                          
                          orcl>
                          there is no need to grant quota on a tablespace if the user already has RESOURCE, which is one reason why you should never grant RESOURCE to anyone. Clearly, there is sonething else going on in your database. Another indication that things are not as they seem is that in 11.2.x by default you do not need quota on a tablespace (or unlimnited tablespace) to create tables, because by default segment creation is deferred. So you get the error only when you try to insert into the table:
                          orcl> create user me identified by me;
                          
                          User created.
                          
                          orcl> grant create session,create table to me;
                          
                          Grant succeeded.
                          
                          orcl> conn me/me
                          Connected.
                          
                          Session altered.
                          
                          orcl> create table t1(c1 date) tablespace system;
                          
                          Table created.
                          
                          orcl> insert into t1 values(sysdate);
                          insert into t1 values(sysdate)
                          *
                          ERROR at line 1:
                          ORA-01950: no privileges on tablespace 'SYSTEM'
                          
                          
                          orcl>
                          I think you need to investigate further. Things are not as they seem.
                          Ok sir i'l investigate further, but my problem is solved.. By the above content i have to know why the error occurs in 11g..Let me check Once...


                          Thank u Sir..