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:
- Grant
- Revoke
Conceptual
figure
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.
Syntax
GRANT { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]
Examples
By viewing the below example, the concept of grant command can be easily understood.
[sql]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)[/sql]
In the above example, the user have been granted only read permissions on tables, but cannot alter or change any values in the table.
Description
The revoke command will cancel all the permissions from the user.
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] ...
Examples
By viewing the below example, the concept of revoke command can be easily understood.
[sql]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[/sql]
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.
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.