Expt.
No:07 DATA CONTROL LANGUAGE (DCL)
Date:
Aim:
To create tables and execute Data Control Language (DCL) commands.
DATA CONTROL LANGUAGE (DCL):
Description:
Data Control Language (DCL) is used to control access to data in the database. DCL includes
commands like GRANT and REVOKE, which are used to give or take away permissions
from users or roles. These commands help in managing database security and controlling
access to the database objects.
List of DCL commands:
1. Grant
2. Revoke
GRANT: This command gives user access privileges to the database.
REVOKE: This command withdraws the user’s access privileges given by using the
GRANT command.
Granting Privileges:
Create statement only creates a new user but does not grant any privileges to the user
account. Therefore, to grant privileges to a user account, the GRANT statement is used.
Syntax:
GRANT privileges_names ON object TO user;
Parameters used:
Privileges_name: These are the access rights or privileges granted to the user.
Object: It is the name of the database object to which permissions are being granted. In the
case of granting privileges on a table, this would be the table name.
User: It is the name of the user to whom the privileges would be granted.
Privileges: The privileges that can be granted to the users are listed below along with the
description:
Create user:
Query:
create user 'new'@'host' identified by 'new@123';
Output:
Query OK, 0 rows affected (0.03 sec)
Granting SELECT Privilege to a User in a Table:
Query:
grant select on employee to 'new'@'host';
Output:
Query OK, 0 rows affected (0.01 sec)
Granting more than one Privilege to a User in a Table:
Query:
grant select,insert on employee to 'new'@'host';
Output:
Query OK, 0 rows affected (0.03 sec)
Granting All the Privilege to a User in a Table:
Query:
grant all on employee to 'new'@'host';
Output:
Query OK, 0 rows affected (0.03 sec)
Granting a Privilege to all Users in a Table:
Query:
grant select on employee to 'new'@'host','admin'@'host';
Output:
Query OK, 0 rows affected (0.02 sec)
Revoking a Privilege to all Users in a Table:
Query:
grant select on employee to 'new'@'host','admin'@'host';
Output:
Query OK, 0 rows affected (0.02 sec)
Revoking Privileges from a Table:
The Revoke statement is used to revoke some or all of the privileges which have been granted
to a user in the past.
Syntax:
REVOKE privileges ON object FROM user;
Parameters Used:
Object:
It is the name of the database object from which permissions are being revoked. In the
case of revoking privileges from a table, this would be the table name.
user: It is the name of the user from whom the privileges are being revoked.
Privileges can be of the following values: Different Ways of revoking privileges from a
user:
Revoking SELECT Privilege to a User in a Table:
Query:
revoke select on employee from 'new'@'host';
Output:
Query OK, 0 rows affected (0.01 sec)
Revoking more than one
Privilege to a User in a Table:
Query:
revoke insert,update on employee from 'new'@'host';
Output:
Query OK, 0 rows affected (0.02 sec)
Revoking All the Privilege to a User in a Table:
Query:
revoke all on employee from 'new'@'host';
Output:
Query OK, 0 rows affected (0.01 sec)
Revoking a Privilege to all Users in a Table:
Query:
revoke select on employee from 'new'@'host','admin'@'host';
Output:
Query OK, 0 rows affected (0.02 sec)
Result:
Thus, the Data Control Language (DCL) commands were executed and the output was
verified successfully.
