DCL Commands in SQL

DCL Commands in SQL:

DCL (Data control Language) commands are used control the operations(privileges) on the database. 

Normally to preform any operation on the database, We need certain privileges. Then only we can access the database. These are related to administration and handles by database admin.

For example to create table, create sequence and create index, we need certain privileges. Privilege are two types

-> System Privileges

->Object Privileges

System privileges includes the database objects creation, creation of session to login and other system related operations.

Object Privileges are all about permission to run any query on table, Like select and update queries.

Below are the two DCL commands

1. GRANT

2. REVOKE

GRANT:   

                Grant command is used to provide the access on any database object to users.

REVOKE:

                Revoke command is used to take back the existing/Granted permissions from users.

Below are few examples.

How To Grant User to Create Table:

 The below command is used to create the table by user. So user can create tables.

  GRANT CREATE TABLE TO username;      

How To Grant User to Create Any Table:

In few cases system won't allow us to create some system reserved tables or database objects.                So in that case we have to use the below Query.

GRANT CREATE ANY TABLE TO username;   

Grant Permission to Create Session:

Post creation of user in Oracle, Session privilege need to be granted to user. Otherwise user can't            able to login. Below is the command to create SESSION.

GRANT CREATE SESSION TO username;           

Grant Permission to Drop table:

 This grant permission will allow user to drop any table form the database. Normal users won't                 have these kind of access, Only Admin can perform this action.

 GRANT DROP ANY TABLE TO username;                

Revoke/Take back the access from user:

Incase if we want to revert or take back the grant permission, Then we have to use Revoke                command. Below is the example

 REVOKE DROP ANY TABLE FROM username;        

If you have any doubts, Please drop a comment.

                                                       Thank You