Successful organizations today view data as their most important strategic asset. In fact, data enables management and stakeholders to gain valuable insights into their business and decide how to better compete with other players on the market. Therefore, the various types of information stored in large databases is crucial for both performance tracking and future goal setting.
On average, tens or hundreds – sometimes even thousands – of employees work with these databases. However, not all of them have the same rights of access, which is a reasonable precaution against security breaches and data leaks. In truth, a business could suffer great losses if sensitive information were to fall into the wrong hands. For this reason, management often appoints qualified professionals to maintain their databases and handle their accessibility within the company.
In general, it is a good idea to put certain permissions and restrictions in place in order to control who has access to a database and what they can do with it, especially on a larger business scale. The query language SQL offers a great solution in the form of the data control language.
What Is DCL?
Essentially, the data control language (DCL) is an SQL syntax that allows you to manage users’ rights in a database through a pair of commands you can implement. Moreover, people who have complete rights to a database are database administrators who can manage user access.
To learn how to create, manipulate, and exercise transaction control with SQL, read our tutorials on DDL, DML, and TCL.
What Are the DCL Commands?
In SQL, DCL has only 2 statements:
- GRANT
- REVOKE
They are intuitive and help you manage what kind of access that users have to your database, such as what SQL commands they can apply.
GRANT Statement
GRANT gives certain permissions to users. You can execute the command with the following syntax:
GRANT type_of_permission ON database_name.table_name TO '@username'@'localhost'With this line of code, you can grant a specific type of permission, like complete or partial access to the resources in a designated data table. In addition, you will be assigning these rights to a person with a username registered at the local server that MySQL has provided with the workbench. What is more, it is based on the machine you’ve using and is commonly known as “localhost” – the domain name for the local IP address.
Naturally, big companies and corporations don’t use this type of server. Instead, their databases lay on external, much more powerful ones that are specifically designed to store large amounts of data. Those servers are not our “localhost” and are accessible on different IP addresses. In our case, that is 127.0.0.1.
How to Apply the GRANT Statement in SQL?
In order to apply the GRANT statement in SQL, first, we need to see how usernames are usually created in SQL. Then, we will grant certain rights to that user. By executing the following line of code, we will create a user “frank” with password “pass”:
CREATE USER 'frank'@'localhost' IDENTIFIED BY 'pass';Say we’d like to allow frank access to certain resources in our database – for example, the table containing sales data. Then, with the following line, they won’t be able to apply anything but the SELECT statement to the “Customers” table from our database:
GRANT SELECT ON sales.customers TO 'frank'@'localhost';Let’s look at what the MySQL workbench looks like when frank logs in from their profile:
As expected, they can only see the “Customers” table in the “Sales” database. What this means is that the user can’t get to the “Sales” or the “Items” table, for instance.
If we try to apply the ALTER statement and the ADD clause, we would obtain an error:
Meanwhile, if we run a SELECT statement, the output of the operation will appear immediately and we will get a sign that the command has been successfully executed:
Now, this following code will grant frank complete access to all the tables of the “Sales” database as indicated by the star symbol:
GRANT ALL ON Sales.* to 'frank'@'localhost';In this way, the user can carry out more operations, including but not limited to:
- CREATE
- DROP
- TRUNCATE
- SELECT
- INSERT
- ALTER
After running this command, you can open frank’s profile and see that more tables are showing in the “Sales” database, not just “Customers”:
Not only will the SELECT statement work now, but so will the ALTER statement:
Moreover, frank can utilize the rest of the DDL and DML statements as well.
REVOKE Statement
We use the REVOKE clause to take permissions and privileges of database users away – in other words, the exact opposite of the GRANT statement. However, their syntax is identical:
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost'Essentially, instead of granting permission to somebody, you can revoke a privilege from them.
With the earlier example, we allowed user frank to apply the SELECT clause to the “Customers” table from the “Sales” database. Now, with REVOKE, you can reverse this operation and, thus, take away their permission:
REVOKE SELECT ON sales.customers FROM 'frank'@'localhost';Additionally, you can also revoke frank’s permission to apply the SELECT command in the “Customers” table:
As a result, they cannot use SELECT on this table anymore – MySQL will throw an error.
DCL: What’s Next?
Knowing how to exercise control on your relational databases with the data control language will undoubtedly be a huge plus for your future data science career. As one of the most in-demand languages, SQL is increasingly required by employers across various industries. So, adding it to your toolbox is certainly a good idea.
Build solid skills with out SQL course.
The 365 Data Science Program offers self-paced courses led by renowned industry experts. Starting from the very basics all the way to advanced specialization, you will learn by doing with a myriad of practical exercises and real-world business cases. If you want to see how the training works, start with our free lessons by signing up below.
