Managing Roles   «Prev  Next»

Lesson 4 Granting system privileges
ObjectiveGrant system privileges to a user.

Granting system privileges

Assign privileges to a user

The SQL GRANT command controls what privileges a user will have. You can issue the GRANT command from SQL*Plus or SQL Developer. View the code below to see what the syntax looks like.
GRANT privilege[, privilege...] TO username 
[WITH ADMIN OPTION];

The username is the user to which you are granting the privileges. The WITH ADMIN OPTION allows that user to further grant the privileges to other users. WITH ADMIN OPTION is described later in this module.
The following Diagram shows a typical example of a grant:
GRANT statement
  1. The GRANT statement is used to grant privileges.
  2. These are privileges that you are granting. They follow the GRANT keyword as a comma-delimited list.
  3. The TO clause indicates the user to whom you are granting the privileges. In this case, they are being granted to the user named coin_admin.

SQL Grant Statement
The following Simulation lets you practice this using the SQL*Plus worksheet.

  1. After starting Security Manager and connecting to the database, you will come to this screen.
  2. Go ahead and type in the command to give the coin_admin user the CREATE SESSION and CREATE TABLE privileges. The CONNECT command will be replaced by what you type.
  3. Now, execute the command by clicking the lightning-bolt on the left side of the screen.

Connect system/ ****@coin

Your GRANT succeeded, and a message to that effect is displayed in the bottom half of the window. This is the end of the simulation. Click the Exit button.

Grant System Privileges

  1. After starting Security Manager and connecting to the database, you will come to this screen. Go ahead and type in the command to give the coin_admin user the CREATE SESSION and CREATE TABLE privileges. The highlighted CONNECT command will be replaced by what you type.
  2. Now, execute the command by clicking the lightning-bolt on the left side of the screen.
  3. Your GRANT succeeded, and a message to that effect is displayed in the bottom half of the window.

Granting Privileges to All Users

You might find yourself in a situation where a privilege would be more aptly granted to all users as opposed to individual users. This is rare, and you should be very cautious when considering granting a particular privilege to all users. To assign this privilege, simply use the command PUBLIC. For example, the following command gives everyone the CREATE SESSION privilege:
GRANT CREATE TABLE TO PUBLIC;

Granting System Privileges - Exercise

Before you move on to the next lesson, click the Exercise link to practice writing GRANT statements.
Granting System Privileges - Exercise