This discussion is archived
11 Replies Latest reply: Jun 15, 2007 3:07 PM by 562711 RSS

Insufficient privileges/can not grant to create tables

562711 Newbie
Currently Being Moderated
I have just created database..

Connected as SYS I tried to Create user/schema as following:
CREATE USER "xsales" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCK
GRANT "CONNECT" TO "ABDO"

Trying to create tables I recieved:
Failed to commit: ORA-01950: no privileges on tablespace 'USERS'

Then I tried to grant CREATE ANY TABLES to the user xsales to enable it to
run the script of create tables,but I recieved
ORA-01917: user or role 'abdo' does not exist

although I connected with xsales succefully

Can anyone help?
  • 1. Re: Insufficient privileges/can not grant to create tables
    427492 Newbie
    Currently Being Moderated
    There are 2 different users here!! 'xsales' and 'ABDO'!?
    You need to define a default tablespace to 'xsales' and grant quota for the user for his default tablespace.

    Message was edited by:
    FeNiCrC_Neil
  • 2. Re: Insufficient privileges/can not grant to create tables
    562711 Newbie
    Currently Being Moderated
    Sorry,
    it is onlu one user ,just wrong edit

    CREATE USER "xsales" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCK
    GRANT "CONNECT" TO "xsales"

    I already defined default tablespace as follow:

    Name XSALES
    Profile DEFAULT
    Authentication Password
    Default Tablespace USERS
    Temporary Tablespace TEMP
    Status UNLOCK
  • 3. Re: Insufficient privileges/can not grant to create tables
    427492 Newbie
    Currently Being Moderated
    Oh ok. If you want to grant unlimited tablespace to xsales on the default tablespace USERS:

    grant unlimited tablespace to xsales; or use OEM to do this.
  • 4. Re: Insufficient privileges/can not grant to create tables
    562711 Newbie
    Currently Being Moderated
    Thanks Neil for patience but seems the problem was not clear,I will restate it:

    The problem is simply that I can not create table in the schema xsales that I just created using dbca

    I tried[u] to create the tables for the XSALES in two ways
    1-logging as SYS from OEM, however I got
    : ORA-01950: no privileges on tablespace 'USERS'

    2-Then I tried to give GRANT CREATE ANY TABLES/TABLESPACES to XSALES user SO I can log as Xsales and create the tables

    I loged as SYS from sqlplus try to give grant.However I recieved :
    [u]ORA-01917: user or role 'XSALES' does not exist

    Knowing that I am sure that the user/schema exist as I could log as XSALES

    I appreciate any help
  • 5. Re: Insufficient privileges/can not grant to create tables
    388131 Explorer
    Currently Being Moderated
    Did you indeed use double quotes while defining the user's name?
    Then that might be the problem (think case sensitivity).

    Rgds,
    Guido
  • 6. Re: Insufficient privileges/can not grant to create tables
    277993 Journeyer
    Currently Being Moderated
    Connect /as sysdba
    select username from dba_users order by username;

    Check what the exact name is to ensure it is there. If it is wrong, then drop the user and recreate it with the correct name.

    Now you have to note that the CONNECT role changed in 10g. So by granting XSALES the CONNECT role does not give them the privilege to create a table.

    BTW - it is better to copy and paste the error messages from your screen without editing it. That will help solve your problem faster
  • 7. Re: Insufficient privileges/can not grant to create tables
    562711 Newbie
    Currently Being Moderated
    No, I defined it without quotes.
  • 8. Re: Insufficient privileges/can not grant to create tables
    562711 Newbie
    Currently Being Moderated
    the user name is correct .....

    I ran the following script succefully:



    CREATE USER xsales PROFILE "DEFAULT" IDENTIFIED BY "********" ACCOUNT UNLOCK;
    GRANT "CONNECT" TO xsales;

    GRANT ALTER ANY CLUSTER TO XSALES;
    GRANT ALTER ANY DIMENSION TO XSALES;
    GRANT ALTER ANY INDEX TO XSALES;
    GRANT ALTER ANY INDEXTYPE TO XSALES;
    GRANT ALTER ANY MATERIALIZED VIEW TO XSALES;
    GRANT ALTER ANY PROCEDURE TO XSALES;
    GRANT ALTER ANY TABLE TO XSALES;
    GRANT ALTER TABLESPACE TO XSALES;
    GRANT ALTER USER TO XSALES;
    GRANT ANALYZE ANY DICTIONARY TO XSALES;
    GRANT CREATE ANY CLUSTER TO XSALES;
    GRANT CREATE ANY DIMENSION TO XSALES;
    GRANT CREATE ANY INDEX TO XSALES;
    GRANT CREATE ANY INDEXTYPE TO XSALES;
    GRANT CREATE ANY MATERIALIZED VIEW TO XSALES;
    GRANT CREATE ANY PROCEDURE TO XSALES;
    GRANT CREATE ANY TABLE TO XSALES;
    GRANT CREATE ANY VIEW TO XSALES;
    GRANT CREATE CLUSTER TO XSALES;
    GRANT CREATE DATABASE LINK TO XSALES;
    GRANT CREATE DIMENSION TO XSALES;
    GRANT CREATE INDEXTYPE TO XSALES;
    GRANT CREATE MATERIALIZED VIEW TO XSALES;
    GRANT CREATE PROCEDURE TO XSALES;
    GRANT CREATE PUBLIC DATABASE LINK TO XSALES;
    GRANT CREATE TABLE TO XSALES;
    GRANT CREATE TABLESPACE TO XSALES;
    GRANT CREATE USER TO XSALES;
    GRANT CREATE VIEW TO XSALES;
    GRANT DELETE ANY TABLE TO XSALES;
    GRANT DROP ANY CLUSTER TO XSALES;
    GRANT DROP ANY DIMENSION TO XSALES;
    GRANT DROP ANY INDEX TO XSALES;
    GRANT DROP ANY INDEXTYPE TO XSALES;
    GRANT DROP ANY MATERIALIZED VIEW TO XSALES;
    GRANT DROP ANY PROCEDURE TO XSALES;
    GRANT DROP ANY TABLE TO XSALES;
    GRANT DROP ANY VIEW TO XSALES;
    GRANT DROP PUBLIC DATABASE LINK TO XSALES;
    GRANT DROP TABLESPACE TO XSALES;
    GRANT DROP USER TO XSALES;
    GRANT EXECUTE ANY INDEXTYPE TO XSALES;
    GRANT EXECUTE ANY PROCEDURE TO XSALES;
    GRANT EXPORT FULL DATABASE TO XSALES;
    GRANT GLOBAL QUERY REWRITE TO XSALES;
    GRANT GRANT ANY OBJECT PRIVILEGE TO XSALES;
    GRANT GRANT ANY PRIVILEGE TO XSALES;
    GRANT IMPORT FULL DATABASE TO XSALES;
    GRANT INSERT ANY TABLE TO XSALES;
    GRANT LOCK ANY TABLE TO XSALES;
    GRANT MANAGE SCHEDULER TO XSALES;
    GRANT MANAGE TABLESPACE TO XSALES;
    GRANT QUERY REWRITE TO XSALES;
    GRANT SELECT ANY DICTIONARY TO XSALES;
    GRANT SELECT ANY TABLE TO XSALES;
    GRANT SELECT ANY TRANSACTION TO XSALES;
    GRANT SYSDBA TO XSALES;
    GRANT CREATE SESSION TO XSALES;
    GRANT UPDATE ANY TABLE TO XSALES;

    -----------------------------

    Then I logged as XSALES to try to CREATE TABLES, still I got:

    SQL> CONN XSALES/**********@ORACLE
    Connected.
    SQL> CREATE TABLE PROD(ID number(6));
    CREATE TABLE PROD(ID number(6))
    *
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'USERS'


    How I can solve that?
  • 9. Re: Insufficient privileges/can not grant to create tables
    277993 Journeyer
    Currently Being Moderated
    ALTER USER XSALES QUOTA 50M ON USERS;

    Then Try again
  • 10. Re: Insufficient privileges/can not grant to create tables
    523455 Newbie
    Currently Being Moderated
    Do like this it is working :---------

    SQL> conn sys/oracle as sysdba
    Connected.

    SQL> create user xsales Profile DEFAULT identified by xsales Default Tablespace USERS
    2 Temporary Tablespace TEMP account unlock;

    User created.

    SQL> grant connect to xsales;

    Grant succeeded.

    SQL> grant create table to xsales;

    Grant succeeded.
    SQL> alter user xsales quota unlimited on users;

    User altered.

    SQL> conn xsales/xsales
    Connected.
    SQL> create table mytab (num number(5));

    Table created.

    In your case what happened is :-------------------

    A user can be assigned (for example within the create user statement) a default object and a default temporary tablespace (with the default tablespace and temporary tablespace clauses). These settings will overwrite default tablespaces that were specified for the database.
    And your user does'nt have quota to create table in User tablespace.
    By granting quota the table created.

    Cheers
  • 11. Re: Insufficient privileges/can not grant to create tables
    562711 Newbie
    Currently Being Moderated
    Thanks Rajesh for the explaination

    I tried it and it work

    thanks for all too