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...)

What Privilege Is Needed for a User to Connect to Oracle Server?

Oracle deny connection to users who has no CREATE SESSION privilege. Try the following tutorial exercise, you will find out how Oracle denies connection:

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

SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.

SQL> disconnect
SQL> CONNECT DEV/developer
ORA-01045: user DEV lacks CREATE SESSION privilege;
  logon denied

Oracle error message is pretty clear.

How To Grant CREATE SESSION Privilege to a User?

If you want give a user the CREATE SESSION privilege, you can use the GRANT command. The following tutorial exercise shows you how to grant DEV the privilege to connect to the server:

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

SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.

SQL> disconnect
SQL> CONNECT DEV/developer
Connected.

How To Revoke CREATE SESSION Privilege from a User?

If you take away the CREATE SESSION privilege from a user, you can use the REVOKE command as shown in the following example script:

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

SQL> REVOKE CREATE SESSION FROM dev;
Revoke succeeded.

SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.

This script restored the CREATE SESSION privilege to user "dev", so you can continue other example scripts below.

How To Lock and Unlock a User Account?

If you want to lock a user account for a short period of time, and unlock it later, you can use the ALTER USER ... ACCOUNT command. The following sample script shows how to use this command:

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

SQL> ALTER USER dev ACCOUNT LOCK;
User altered.

SQL> disconnect
SQL> CONNECT DEV/developer
ORA-28000: the account is locked

SQL> disconnect
SQL> connect SYSTEM/fyicenter

SQL> ALTER USER dev ACCOUNT UNLOCK;
User altered.

SQL> disconnect
SQL> CONNECT DEV/developer
Connected.

What Privilege Is Needed for a User to Create Tables?

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.

(Continued on next part...)

Part: 1 2 3 4 5 6

More...