Description
To perform DML operations on a table, one must have permissions those permissions are called Privileges. These privileges can be control efficiently by using DCL statements. SQL Data Control Language statements are of two types:
- Grant
- Revoke
Conceptual
figure
Description
GRANT is used to grant permissions to the clients. In the SQL 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 SQL 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 understand.
[c]sql>grant select on sample.* to reader@localhost identified by 'secret';
Query OK,0 rows affected
sql>exit;
Bye
D:\SQL\bin>sql -u reader -p
sql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employee |
| sql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
sql> select * from employee;
+--------+----------+-------+
| emp_id | emp_name |salary |
+--------+----------+-------+
| 1001 | mike | 12000 |
| 1002 | maze | 13000 |
| 1003 | jack | 14000 |
+--------+----------+-------+
3 rows in set (0.04 sec)[/c]
In the above example, the user secret have been granted only read permissions on tables, where the user 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.
[c]sql> 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)
sql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'david'@'localhost';
Query OK, 0 rows affected (0.00 sec)
sql> show revoke for 'david'@'localhost';
ERROR 1064 (42000): there is no localhost user[/c]
In the above example, the local host user david as assigned read permissions on all the tables. And by using revoke command all the grant permissions have been revoked on local host david.
Key Points
- To perform DML operation on a table, permissions are required which are called as privileges.
- Grant - Grant permissions to users.
- Revoke - Revoke permissions from users.