This discussion is archived
9 Replies Latest reply: Jan 1, 2013 9:11 PM by Venkateshj RSS

Roles Creating and assigning to user

Venkateshj Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    Hi,

    What is the error message you get?
  • 2. Re: Roles Creating and assigning to user
    Venkateshj Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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..

Legend

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