 | This articleneeds additional citations forverification. Please helpimprove this article byadding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "Data control language" – news ·newspapers ·books ·scholar ·JSTOR(July 2016) (Learn how and when to remove this message) |
Adata control language (DCL) is a syntax similar to a computerprogramming language used to control access to data stored in a database (authorization). In particular, it is a component ofStructured Query Language (SQL). Data Control Language is one of the logical group in SQL Commands.SQL[1] is the standard language for relational database management systems. SQL statements are used to perform tasks such as insert data to a database, delete or update data in a database, or retrieve data from a database.
Though database systems use SQL, they also have their own additional proprietary extensions that are usually only used on their system. For example, Microsoft SQL server uses Transact-SQL (T-SQL), which is an extension of SQL. Similarly, Oracle uses PL-SQL, which an Oracle-specific SQL extension. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.
Examples of DCL commands include the SQL commands:
The operations for which privileges may be granted to or revoked from a user or role apply to both theData definition language (DDL) and theData manipulation language (DML), and may includeCONNECT,SELECT,INSERT, UPDATE,DELETE,EXECUTE, andUSAGE.
InMicrosoft SQL Server there are four groups of SQL commands:[2]
DCL commands are used for access control and permission management for users in the database. With them we can easily allow or deny some actions for users on the tables or records (row level security).
DCL commands are:[2]
For example: GRANT can be used to give privileges to user to do SELECT, INSERT, UPDATE and DELETE on a specific table or multiple tables.
The REVOKE command is used to take a privilege away (default) or revoking specific command like UPDATE or DELETE based on requirements.
GrantSELECT,INSERT,UPDATE,DELETEonEmployeesToUser1RevokeINSERTOnEmployeesToUser1DenyUpdateOnEmployeestoUser1
In the first example, GRANT gives privileges to user User1 to do SELECT, INSERT, UPDATE and DELETE on the table named Employees.
In the second example, REVOKE removes User1's privileges to use the INSERT command on the table Employees.
DENY is a specific command. We can conclude that every user has a list of privilege which is denied or granted so command DENY is there to explicitly ban you some privileges on the database objects.:
Oracle Database divide SQL commands to different types. They are.
For details referOracle-[3]TCL
Data definition language (DDL) statements let you to perform these tasks:
So Oracle Database DDL commands include theGrant andrevoke privileges which is actually part of Data control Language in Microsoft SQL server.
Syntax for grant and revoke in Oracle Database:
GRANTSELECT,INSERT,UPDATE,DELETEONdb1.EmployeeTOuser1;REVOKESELECT,INSERT,UPDATE,DELETEONdb1.EmployeeFROMuser1;
Transaction control statements manage changes made by DML statements. The transaction control statements are:
MySQL server they divide SQL statements into different type of statement
For details referMySQL Transactional statements[4]
The grant, revoke syntax are as part of Database administration statementsàAccount Management System.
The GRANT statement enables system administrators to grant privileges and roles, which can be granted to user accounts and roles. These syntax restrictions apply:
The REVOKE statement enables system administrators to revoke privileges and roles, which can be revoked from user accounts and roles.
REVOKEINSERTON*.*FROM'jeffrey'@'localhost';REVOKE'role1','role2'FROM'user1'@'localhost','user2'@'localhost';REVOKESELECTONworld.*FROM'role3';GRANTALLONdb1.*TO'jeffrey'@'localhost';GRANT'role1','role2'TO'user1'@'localhost','user2'@'localhost';GRANTSELECTONworld.*TO'role3';
InPostgreSQL, executing DCL istransactional, and can be rolled back.
Grant andRevoke are the SQL commands are used to control the privileges given to the users in a Databases
SQLite does not have any DCL commands as it does not have usernames or logins. Instead, SQLite depends onfile-system permissions to define who can open and access a database.[5]
| Types | |
|---|---|
| Concepts | |
| Objects | |
| Components | |
| Functions | |
| Related topics | |
