MySQL - SPLessons

MySQL DCL

Chapter 13

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL DCL

MySQL DCL

shape Description

To perform DML operations on a table permissions are required and are called Privileges. These privileges can be controlled efficiently by using MySQL DCL statements.

MySQL Data Control Language is similar to SQL Data Control Language and these are classified into two types:

shape Conceptual
figure

Grant

shape Description

Grant is used to grant permissions to the clients.In the MySQL database, it offers both the server and client a great amount of control privileges. At the server side of the procedure, it incorporates the possibility for the server to control certain customer benefits over the MySQL database away and reducing their connection permissions from the database or giving limited authorizations for a particular table.

shape Syntax

GRANT { ALL | statement [ ,…n ] }
TO security_account [ ,…n ]

shape Examples

By viewing the below example, the concept of grant command can be easily understood.

mysql>grant select on sample.* to reader@localhost identified by 'secret';
Query OK,0 rows affected 
mysql>exit;
Bye
D:\MySQL\bin>mysql -u reader -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employee           |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)
mysql> select * from employee;
+--------+----------+-------+
| emp_id | emp_name |salary |
+--------+----------+-------+
|   1001 | mike     | 12000 |
|   1002 | maze     | 13000 |
|   1003 | jack     | 14000 |
+--------+----------+-------+
3 rows in set (0.04 sec)

In the above example, the user have been granted only read permissions on tables, but cannot alter or change any values in the table.

Revoke

shape Description

The revoke command will cancel all the permissions from the user.

shape Syntax

REVOKE
<priv_type> [<column_list>]
[ priv_type [<column_list>]] …
ON [object_type] priv_level
FROM user [user] …

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [ user] …

shape Examples

By viewing the below example, the concept of revoke command can be easily understood.

mysql> SHOW GRANTS FOR 'david'@'localhost';
+----------------------------------------------------------------------+
| Grants for david@localhost                                           |
+----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'david'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'david'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show revoke for 'david'@'localhost';
ERROR 1064 (42000): there is no localhost user

In the above example, the localhost user david is assigned read permissions on all the tables and by using revoke command all the grant permissions have been revoked on localhost david.  

Summary

shape Key Points

  • To perform DML operation on a table, permissions are required those permissions are called as privileges.
  • Grant – Grant  permissions to users.
  • Revoke – Revoke permissions from users.