Data Control Language

It is the 3rd sub language available in T-SQL.It is used to control the data between different user accounts. It includes the following statements.

1. GRANT Statement

2. REVOKE Statement

1. Grant Statement: This statement is used to grant the permissions (INSERT,SELECT,UPDATE,DELETE) on a specific table to different user accounts.

Syntax:

GRANT {ALL/SPECIFIC PERMISSIONS} ON TABLENAME TO USER ACCOUNT (S)[WITH GRANT OPTION]

WITH GRANT OPTION: When any user got the permission on a specific table from other user with this option, then that user can grant the permission on that same table to another user account. At that time sub user acts as owner.

Example:

GRANT ALL ON EMP TO BABBU WITH GRANT OPTION

From the above statement BABBU user account got all permission on EMP table from SA user account.Mean time BABBU can give the permission on EMP to another user account because he got the permission WITH GRANT OPTION.

Example: GRANT INSERT,SELECT ON EMP TO JOHNSON

Now JOHNSON can perform select and insert operations on EMP table. But JOHNSON cannot perform update and delete operations on EMP table because he does not have the corresponding permissions.

REVOKE Statement: This statement is used to revoke the permission (INSERT,SELECT,UPDATE,DELETE) on a specific table from different user accounts.

Syntax:

REVOKE {ALL/SPECIFIC PERMISSIONS} ON TABLENAME FROM USER ACCOUNT (S) [CASCADE]

CASCADE: Using this option we can destroy the communication link between user account more over from the main user it self we can revoke the permission from all sub users.

Example: REVOKE ALL ON EMP FROM JOHNSON CASCADE

The above statement revokes the permissions on EMP table from BABBU and JOHNSON. Now BABBU and JOHNSON users cannot access EMP Table.

Data Control Language