RelationalDBDesign RelationalDBDesign


Creating Users   «Prev  Next»
Lesson 9 Granting CREATE SESSION privileges
Objective Allow new users to connect to the database.

Granting CREATE SESSION Privileges

If you have been following along in your database, and try to connect as the user that you just created, you will find that you cannot Even though you have created a user, that user will not be able to connect until you have granted the CREATE SESSION privilege.
You can grant that privilege to the COIN_ADMIN user with the following command:
GRANT CREATE SESSION TO COIN_ADMIN;
After granting this privilege, you will be able to connect using the new user-id. Oracle has dozens of system privileges in addition to CREATE SESSION. You will learn more about them, as well as how to manage them, in the next module.

Grant User Privileges

Once we have created a user, we probably want that user to be able to do something in our database. A created user has no privileges, they can not even connect to the database. In this section we will address this problem by learning how to give users the privileges they need to actually get some work done. First we will look at system privileges, followed by object privileges. We will then look at how you can use roles to make user administration much easier.

Oracle System Privileges

System privileges allow the user to perform system level activities. This might include such things as being able to actually connect to the system, or it might include the ability to do things like create objects in schemas other than your own schema. In the next sections we will discuss the grant command, which you use to grant system privileges. Also the revoke command is used to revoke privileges from users.

Granting Oracle System Level Privileges

The grant command is used to grant system level privileges. System level privileges are those privileges that you need to actually do something on the system. For example you grant system level privileges to a user so that they can:
  1. Connect to the database (create session)
  2. Create objects (create table, create index)
  3. Perform DBA activities, like backup the database (SYSDBA, SYSOPER)
  4. Alter session related parameters (alter session)
Basically, even if you create a user account, it will not be able to do anything until you have granted it a basic set of privileges. Typically you would always grant the create session privilege so that the user can connect to the database.

The grant command is pretty simple to use, you just need to know what privilege you want to grant and who you want to grant that privilege to.
GRANT INDEX ON < table1> 
TO <user1>;