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:

sql
Kopier kode
9
1
2
3
GRANT privileges
ON object
TO {user | role | PUBLIC};

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:

sql
Kopier kode
GRANT role TO user;

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

sql
Kopier kode
9
1
2
3
REVOKE privileges
ON object
FROM {user | role | PUBLIC};

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.

sql
Kopier kode
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Create role
CREATE ROLE bank_manager;

-- Grant privileges to bank_manager role
GRANT SELECT, INSERT, UPDATE, DELETE ON BankAccounts TO bank_manager;
GRANT SELECT, INSERT ON UserLogs TO bank_manager;

-- Create users
CREATE USER john WITH PASSWORD 'password123';
CREATE USER jane WITH PASSWORD 'password456';

-- Assign roles to users
GRANT bank_manager TO john;
GRANT bank_manager TO jane;

Now we can revoke some of the granted privileges:

sql
Kopier kode
9
1
2
3
4
5
6
-- Revoke privileges from bank_manager role
REVOKE UPDATE, DELETE ON BankAccounts FROM bank_manager;
REVOKE INSERT ON UserLogs FROM bank_manager;

-- Revoke bank_manager role from john
REVOKE bank_manager FROM john;