Data control language

Last updated

A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database (authorization). In particular, it is a component of Structured 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.

Contents

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 the Data definition language (DDL) and the Data manipulation language (DML), and may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, and USAGE.

Microsoft SQL Server

In Microsoft 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]

GRANT
gives specified permissions for the table (and other objects) to, or assigns a specified role with certain permissions to, specified groups or users of a database;
REVOKE
takes away specified permissions for the table (and other objects) to, or takes away a specified role with certain permissions to, specified groups or users of a database;
DENY
denies a specified permission to a security object.

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.

Example

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

Oracle Database divide SQL commands to different types. They are.

For details refer Oracle- [3] TCL      

Data definition language (DDL) statements let you to perform these tasks:

So Oracle Database DDL commands include the Grant and revoke privileges which is actually part of Data control Language in Microsoft SQL server.

Syntax for grant and revoke in Oracle Database:

Example

GRANTSELECT,INSERT,UPDATE,DELETEONdb1.EmployeeTOuser1;REVOKESELECT,INSERT,UPDATE,DELETEONdb1.EmployeeFROMuser1;

Transaction Control Statements in Oracle

Transaction control statements manage changes made by DML statements. The transaction control statements are:

MySQL

MySQL server they divide SQL statements into different type of statement

For details refer MySQL 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.

Examples

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';

In PostgreSQL, executing DCL is transactional, and can be rolled back.

Grant and Revoke 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 on file-system permissions to define who can open and access a database. [5]

See also

Related Research Articles

<span class="mw-page-title-main">PostgreSQL</span> Free and open-source object relational database management system

PostgreSQL also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transactions with atomicity, consistency, isolation, durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is supported on all major operating systems, including Windows, Linux, macOS, FreeBSD, and OpenBSD, and handles a range of workloads from single machines to data warehouses, data lakes, or web services with many concurrent users.

Structured Query Language (SQL) is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

<span class="mw-page-title-main">Data definition language</span> Syntax for defining data structures

In the context of SQL, data definition or data description language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining data structures, especially database schemas. Common examples of DDL statements include CREATE, ALTER, and DROP. If you see a .ddl file, that means the file contains a statement to create a table. Oracle SQL Developer contains the ability to export from an ERD generated with Data Modeler to either a .sql file or a .ddl file.

A sublanguage is a subset of a language. Sublanguages occur in natural language, computer programming language, and relational databases.

Dataphor is an open-source truly-relational database management system (RDBMS) and its accompanying user interface technologies, which together are designed to provide highly declarative software application development. The Dataphor Server has its own storage engine or it can be a virtual, or federated, DBMS, meaning that it can utilize other database engines for storage.

A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database. A DML is often a sublanguage of a broader database language such as SQL, with the DML comprising some of the operators in the language. Read-only selecting of data is sometimes distinguished as being part of a separate data query language (DQL), but it is closely related and sometimes also considered a component of a DML; some operators may perform both selecting (reading) and writing.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its creator.

The Access Database Engine is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

In SQL, the TRUNCATE TABLE statement is a data manipulation language (DML) operation that deletes all rows of a table without causing a triggered action. The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the SQL:2008 standard, as the optional feature F200, "TRUNCATE TABLE statement".

A check constraint is a type of integrity constraint in SQL which specifies a requirement that must be met by each row in a database table. The constraint must be a predicate. It can refer to a single column, or multiple columns of the table. The result of the predicate can be either TRUE, FALSE, or UNKNOWN, depending on the presence of NULLs. If the predicate evaluates to UNKNOWN, then the constraint is not violated and the row can be inserted or updated in the table. This is contrary to predicates in WHERE clauses in SELECT or UPDATE statements.

Data Mining Extensions (DMX) is a query language for data mining models supported by Microsoft's SQL Server Analysis Services product.

The SQL From clause is the source of a rowset to be operated upon in a Data Manipulation Language (DML) statement. From clauses are very common, and will provide the rowset to be exposed through a Select statement, the source of values in an Update statement, and the target rows to be deleted in a Delete statement.

Apache Empire-db is a Java library that provides a high level object-oriented API for accessing relational database management systems (RDBMS) through JDBC. Apache Empire-db is open source and provided under the Apache License 2.0 from the Apache Software Foundation.

Java Database Connectivity (JDBC) is an application programming interface (API) for the Java programming language which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform, from Oracle Corporation. It provides methods to query and update data in a database, and is oriented toward relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.

In database management systems (DBMS), a prepared statement, parameterized statement, is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, TimesTen in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

The syntax of the SQL programming language is defined and maintained by ISO/IEC SC 32 as part of ISO/IEC 9075. This standard is not freely available. Despite the existence of the standard, SQL code is not completely portable among different database systems without adjustments.

Data query language (DQL) is part of the base grouping of SQL sub-languages. These sub-languages are mainly categorized into four categories: a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a data manipulation language (DML). Sometimes a transaction control language (TCL) is argued to be part of the sub-language set as well.

References

  1. ↑ "The SQL Standard – ISO/IEC 9075:2016 (ANSI X3.135) – ANSI Blog". The ANSI Blog. 2018-10-05. Retrieved 2020-09-19.
  2. 1 2 Love, Aubrey (March 28, 2023). "SQL Clause, Statement, Command, Expression and Batch Defined". MSSQLTips. Retrieved 2025-01-26.
  3. ↑ "Database SQL Language Reference". docs.oracle.com. Retrieved 2020-09-19.
  4. ↑ "MySQL :: MySQL 8.0 Reference Manual :: 13.7.1 Account Management Statements". dev.mysql.com. Retrieved 2020-09-19.
  5. ↑ Kreibich, J.A., 2010. Using SQLite, O’Reilly.
This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.