Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

How To Assign a Tablespace to a Users?

When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's default tablespace, and assigns 4MB of space to dev:

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

SQL> ALTER USER dev DEFAULT TABLESPACE USERS;
User altered.

SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> CREATE TABLE fyi (id NUMBER);
Table created.

SQL> DROP TABLE fyi;
Table dropped.

SQL> CREATE TABLE fyi (id NUMBER);
Table created.

As you can see, "dev" can create and drop tables now. You can also let "dev" to create tables in any tablespace without any restriction by granting him the UNLIMITED TABLESPACE system privilege.

What Privilege Is Needed for a User to Create Views?

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

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

SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
ORA-01031: insufficient privileges

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> GRANT CREATE VIEW TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.

SQL> DROP VIEW fyi_view;
View dropped.

SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.

As you can see, "dev" can create and drop views now.

What Privilege Is Needed for a User to Create Indexes?

For a user to create indexes, he/she needs the same privilege as the creating tables. Just make sure he/she has the CREATE TABLE privilege. The following tutorial exercise gives you a good example on creating view privilege:

>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter

SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer

SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.

SQL> DROP INDEX fyi_index;
Index dropped.

SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.

This exercise assumes that you followed previous exercises in the FAQ collection.

(Continued on next part...)

Part: 1 2 3 4 5 6

More...