SQL - SPLessons

SQL Data Control Language

Chapter 49

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Data Control Language

SQL Data Control Language

shape 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:

shape Conceptual
figure

Grant

shape 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.

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 understand.

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)

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.

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.

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

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.  

Summary

shape 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.