Privilege to Create Tables in Oracle

Q

What Privilege Is Needed for a User to Create Tables in Oracle?

✍: FYIcenter.com

A

To be able to create tables in a user's own schema, the user needs to have the CREATE TABLE privilege, or the CREATE ANY TABLE privilege, which is more powerful, and allows the user to create tables in other user's schema. The following tutorial exercise gives you a good example on CREATE TABLE privilege:

>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer

SQL> CREATE TABLE fyi (id NUMBER);
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> CREATE TABLE fyi (id NUMBER);
ORA-01950: no privileges on tablespace 'SYSTEM'

The above error message tells that user "dev" is not allowed to use the tablespace "SYSTEM". See the next question for answers.

⇒ Assign a Tablespace to a User in Oracle

⇐ Lock and Unlock a User Account in Oracle

⇑ Managing Oracle User Accounts, Schema and Privileges

⇑⇑ Oracle Database Tutorials

2019-06-29, 3886🔥, 0💬