SQL Data Control Language (DCL)

Introduction to DCL

  • DCL stands for Data Control Language.
  • DCL is used to control user access in a database.
  • This command is related to the security issues.
  • Using DCL command, it allows or restricts the user from accessing data in database schema.
    • DCL commands are as follows,
    1. GRANT
    2. REVOKE
  • It is used to grant or revoke access permissions from any database user.

1. GRANT COMMAND

  • GRANT command gives user's access privileges to the database.
  • This command allows specified users to perform specific tasks.
Syntax:
GRANT <privilege list>
ON <relation name or view name>
TO <user/role list>;

Example : GRANT Command

GRANT ALL ON employee
TO ABC;
[WITH GRANT OPTION]


In the above example, user 'ABC' has been given permission to view and modify the records in the 'employee' table.

2. REVOKE COMMAND

  • REVOKE command is used to cancel previously granted or denied permissions.
  • This command withdraw access privileges given with the GRANT command.
  • It takes back permissions from user.
Syntax:
REVOKE <privilege list>
ON <relation name or view name>
FROM <user name>;

Example : REVOKE Command

REVOKE UPDATE
ON employee
FROM ABC;

Difference between GRANT and REVOKE command.

GRANTREVOKE
GRANT command allows a user to perform certain activities on the database.REVOKE command disallows a user to perform certain activities.
It grants access privileges for database objects to other users.It revokes access privileges for database objects previously granted to other users.
Example:

GRANT privilege_name
ON object_name
TO

{
     user_name|PUBLIC|role_name
}

[WITH GRANT OPTION];		Example:

REVOKE privilege_name
ON object_name

FROM
{
     user_name|PUBLIC|role_name
}
▲