DATA CONTROL LANGUAGE (DCL)

INTRODUCTION to DCL

• DATA CONTROL LANGUAGE is known as DCL.
• DCL Statement is used for securing the database.
• DCL Statement control access to database.
• As data is important part of whole database system we must take proper steps to check that no invalid user access the data and invalidate the information created by us. To kept such a kind of watch we must have to execute certain DCL statement.
• Two main DCL statement are Grant and Revoke.

GRANT Statement- Grant privilege(Rights which are to be allocated) is used when we want our database to share with other users, with certain type of right granted to him. Consider that if we want our enduser to have only access privilege to our database, we can grant it by executing command.
- Grant privilege is assigned not only on table object, but also views, synonyms, indexes, sequences,etc.


Syntax:
GRANT PRIVILEGES ON
TO 

Example
1)SQL> grant select on
emp to endusers;
- here emp is the table_name whose access right is being allocated to the user who logged on as endusers.
11)SQL>grant insert,select,delete on
emp to operators;
- here user who logged on as operators are granted access, insertion and deletion right on the database.
111)SQL>grant insert (empno, ename, job) on
emp to endusers;
- In some case, we require to hide the information to particular users, this can be achived by grant as in the above command we want to hide the detail of employee salary to endusers, so by executing above command we can hide the information regarding empsalary to the endusers.


REVOKE Statement- Revoke privilege(Rights which are to be de-allocated) is used when we want our database to stop sharing the information with other users, with certain type of right revoked to him. Consider that if we want our operators to have only access privilege to our database, we can revoke it by executing command.
- Revoke privilege is assigned not only on table object, but also views, synonyms, indexes, sequences,etc.

Syntax
REVOKE PRIVILEGES ON
FROM 

example
1)SQL> revoke insert, delete on
emp from operators;

Posted in: SQL