Granting System Privileges and 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
The GRANT statement is used to grant privileges.
These are privileges that you are granting. They follow the GRANT keyword as a comma-delimited list.
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
GRANT CREATE SESSION, CREATE TABLE,
CREATE INDEX, ALTER SESSION TO coin_admin;
This SQL command grants the `CREATE SESSION`, `CREATE TABLE`, `CREATE INDEX`, and `ALTER SESSION` privileges to the user `coin_admin` in an Oracle database.
GRANT: The GRANT statement is used to grant privileges.
CREATE SESSION, CREATE TABLE, CREATE INDEX, ALTER SESSION: These are privileges that you are granting. They follow the GRANT keyword as a comma-delimited list.
TO coin_admin: 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.
The following series of images lets you practice this using the SQL*Plus worksheet.
1) Connected
2) GRANT CREATE SESSION, CREATE TABLE TO COIN_ADMIN
3) GRANT succeeded
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 CONNECT command will be replaced by what you type.
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
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.
Now, execute the command by clicking the lightning-bolt on the left side of the screen.
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:
