Data Control Language

Data Control Language (DCL) is a subset of SQL used to control access to data stored in a relational database management system.
DCL commands are primarily concerned with granting or revoking privileges on database objects such as tables, views, and schemas.

DCL commands

The two main DCL commands are:

1. GRANT: This command is used to give specific privileges to users or roles;

2. REVOKE: This command is used to remove specific privileges from users or roles that have been previously granted.

Privileges

Objects

Implementation

To grant some privileges for an object in SQL for a particular user we can use the following statement:

There are 3 types of roles in DB to which you can grant some privileges:

• user: An individual database user;
• role: A database role, a named group of privileges that can be assigned to users (e.g. admin, developer, analyst);
• PUBLIC: A special keyword that grants the specified privileges to all users.

We can grant a role to a user using the following statement:

Finally, we can revoke previously granted privileges using the following statement:

Example

Here are some examples of how to use the GRANT command to assign different privileges on the bankaccounts and userlogs tables to different roles and users.

Now we can revoke some of the granted privileges: