HOW TO GRANT TABLE SPACE

[ahmed eitah]

I CREATE A USER IN ORACLE DB USING ROLE AS SYSDBA AND I GRANT FOR HIM CTREATE TABLE AND SESSION.(GRANT CREATE TABLE TO NEW_USER;)WHEN I CONNECT USING THIS NEW_USER AND TRYED TO CREATE TABLE I GOT THE FOLLOWING MESSAGE "ORA 01950 NO PRIVILAGES ON SYSTEM TABLES".IF I CONNECT USING SCOTT/TIGER I CAN CREATE TABLES.
HOW CAN I GRANT CREATE TABLES FOR ANY USER WHAT IS THE PROCEDURE.

[ddf]

On May 17, 4:35 am, ahmed eitah <zouzu7...@yahoo.com> wrote:
> I CREATE A USER IN ORACLE DB USING ROLE AS SYSDBA AND I GRANT FOR HIM CTREATE TABLE AND SESSION.(GRANT CREATE TABLE TO NEW_USER;)WHEN I CONNECT USING THIS NEW_USER AND TRYED TO CREATE TABLE I GOT THE FOLLOWING MESSAGE "ORA 01950 NO PRIVILAGES ON SYSTEM TABLES".IF I CONNECT USING SCOTT/TIGER I CAN CREATE TABLES.
> HOW CAN I GRANT CREATE TABLES FOR ANY USER WHAT IS THE PROCEDURE.

The source is clearly listed in the text for the ORA-01950 error:

"No privileges on SYSTEM tablespace."

You've assigned the SYSTEM tablespace as the default tablespace for
your user (so this is probably a 9.2.0.x or earlier release of Oracle)
yet you failed to grant a quota on that tablespace to NEW_USER. Your
first mistake was not assigning a default tablespace (different from
SYSTEM, your most important tablespace when it comes to running your
Oracle database) to your new user and your second was in assuming your
user had privileges on said default tablespace. The correct create
table statement is shown below:

create user new_user identified by new_pass
default tablespace users
temporary tablespace temp
quota unlimited on users;

Assign this user to the USERS tablespace, grant this user a quota on
that tablespace and you'll find you can create tables.


David Fitzjarrell