DCL Commands in SQL SERVER

DCL: Data Control Language

These commands are used to control the data access. That is DCL commands enforce the security to data.

By granted the access to only required users data can be made as secured.
Only Database administrator can grant/revoke access permissions to the database. By granting access to only authorised user data safety can be maintained.

Access can be granted/revoked in 2ways:
  1. Grant/Revoke access to an individual user
  2. Grant/Revoke access to a group
DCL commands are: GRANT ,REVOKE and DENY.

GRANT: Grants access to the database. Only admin have the privileges to grant access to user/group.

Syntax for the Grant Command is:

GRANT  <Permission Type> ON  <Securable Object Name> TO <user/Group>

Permissions Types vary from object to object.

For example if the object is Database, then following permission types are possible :

BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, DROP DATABASE, CREATE DEFAULT,DROP DEFAULT, CREATE FUNCTION,DROP FUNCTION, CREATE TABLE, DROP TABLE CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW ....etc.,

Similarly Permission type vary based on the object type.

Example:

Lets assume that I have to give only SELECT permissions to a user called Ken for my table FRIENDS , then the following command will be used to grant access to Ken.

GRANT SELECT ON FRIEND TO Ken

Similarly INSERT and Update permission can also be granted if required.

REVOKE:

Revoke command is used to revoke back the permissions which were granted earlier.

Syntax for REVOKE command is very similar to GRANT command except for the keyword.

REVOKE <Permission Type> ON  <Securable Object Name> TO <user/Group>

Example:
As part of Grant permissions i gave SELECT privileges to Ken for my table named FRIENDS.
Now, we will how we can revoke those access permissions.

REVOKE SELECT ON FRIENDS TO Ken

DENY:

Using DENY Command a particular user/group can be stopped from performing a certain task.

Syntax for DENY is same GRANT and REVOKE.

Here is the example which Deny stops/prevent (deny) Ken from performing update task on FRIENDS table.

DENY UPDATE ON FRIENDS TO Ken

GRANT/REVOKE/DENY Commands can be used to control all database objects like database, tables, views,procedures,views, columns, etc.,

Summary:

In this session we learnt about various DCL Commands.

Commands Learnt:

GRANT,REVOKE, DENY,ON,TO

This entry was posted in . Bookmark the permalink.

One Response to DCL Commands in SQL SERVER