Data Control Language can also call DCL. SQL Server not only provides several different objects for customized database management but also it supports user concept or schema concept to protect those objects from invalid users. No user can be created or manipulated from a general user rather than user creation and user manipulation by DBA user. So SQL server provides Data Control Language for control the above scenario.
It privileges (the right to access data) are required to perform all database operations such as creating/updating/deleting tables, views, or sequences.
Contents
Need Of Data Control Language commands
- Unauthorized access to data must be prevented to reach security in our databases.
- The DCL command maintains the database effectively, no one other than the database administrator is allowed to access the data without permission.
- These commands provide the data administrator with the flexibility to set and delete database permissions to other user.
Working with Privilege
Whenever a user is created in database user is not going to be activated unless and until some privilege are assigned in it.
- System Privilege
- Object Privilege
System privileges are those which are granted individual objects what can be done by the object and what cannot be.
The frequently used system privileges are create table, create view, similarly index and procedure.
NOTE:
Among the above system privileges create session is privilege is the important privilege which is responsible to activate a user by creating session ID.
Frequently used object privileges are select, update, insert, delete, execute.
Above privilege can either be assigned to an user or object likewise revoked from an user or object.
Commands in Data Control Language
It includes commands such as GRANT and REVOKE which are mainly deal with the rights, permissions, and other controls of the database system.
- Grant
- Revoke
Better to read the previous topic to get good knowledge about SQL.
Data Definition Language (DDL) in SQL
Data Manipulated Language (DML) in SQL
Grant
This command is used to allow the user to perform special operations on a particular object. If you are a database administrator and you want to restrict access to the user such as who only sees data or can only update data. You can give privilege permission to users as you wish.
Syntax:
GRANT privilege_list
ON Object_name
TO user_name;
Revoke
This command is used to withdraw permission/access from the user. If you want to return permission from the database that you gave to users at that time then you need to run the revoce command.
Syntax:
REVOKE privilege_list
ON object_name
FROM user_name;
The below commands are granted to the user as a Privilege List:
EXECUTE
UPDATE
SELECT
DELETE
ALTER
ALL
Advantages Of Data Control Language commands
- This allows the user to restrict access to data in the database.
- When data is exposed to multiple users it ensures security in the database.
- It is responsibility of the data owner or data administrator to maintain the authority of grant and revoke
Reference Link
Data Control Language in Wiki
