As part of design it is important to have different users and assign each user just the right amount of access they need. SQL uses GRANT statements to provide users with the specified amount of access.
Let us understand this better using examples from our simple Blue Sky Toys database.
We create 3 users to handle different aspects of the entire database. First, we create a root (let us call it bluesky_root) for the database who gets to own all the tables belonging to the blue_sky_toys database; note that this user is different from the root of the MySQL database server. Second, we create an admin (let us call it bluesky_admin) who can add employees and stores to the database. Third, we create a stock user (let us call it bluesky_stock), who gets to update inventory and items.
This clean separation of privileges can be advantageous for application design. The bluesky_admin user handles administrative roles like adding/updating employees and stores. The bluesky_stock handles all updates related to stocks like adding/updating items and inventory. If we were to build a web application for this, let us say, using PHP, then from PHP we can allow these users to login using different pages.
Or, when they login, we identify who these users are and then establish a connection using either bluesky_admin or bluesky_stock as MySQL users. This would automatically restrict access of each of these users precisely as per our design!
Let us get started with creating these 3 users!
First, let us create the user, bluesky_root. We use "CREATE USER" to create this user and then, use the GRANT option to assign privileges to this user.
mysql> CREATE USER bluesky_root@localhost IDENTIFIED BY "bst_password_root"; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON blue_sky_toys.* TO bluesky_root@localhost; Query OK, 0 rows affected (0.00 sec) mysql> GRANT GRANT OPTION ON blue_sky_toys.* TO bluesky_root@localhost; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR bluesky_root@localhost; +-------------------------------------------------------------------------------------------+ | Grants for bluesky_root@localhost | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_root'@'localhost' IDENTIFIED BY | | PASSWORD '*C01FDB9E80BCDD3AEE748679365023271B329227' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.* TO 'bluesky_root'@'localhost' WITH GRANT OPTION | +-------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Let us understand the above assignment of GRANTS.
First, we assign two privileges to bluesky_root user. The first privilege is "GRANT ALL ON blue_sky_toys.*" -- this means that we assign all access to the bluesky_root user. In the query, "blue_sky_toys.*" represents all the tables present in the blue_sky_toys database; with this GRANT, the bluesky_root user gets all access for all the (four) tables of the blue_sky_toys: blue_sky_toys.employees, blue_sky_toys.inventory, blue_sky_toys.items, and blue_sky_toys.stores.
The second privilege is that of "GRANT GRANT OPTION" -- this means that this user can assign GRANT option to other users as well. In other words, bluesky_root can even assign privileges to other users. Please note that we have added this to demonstrate the usage of this grant type. However, in real life deployment, this may not be necessary since one can always depend upon the global MySQL root user to assign privileges to other users.
Next, "FLUSH PRIVILEGES" asks the MySQL to reload the grant tables from the "mysql" database. With this, the new privileges assigned to user become active. Lastly, we can use "SHOW GRANTS FOR bluesky_root@localhost" command to see the assigned privileges for the user bluesky_root.
Now, if we were to login with bluesky_root user, we would see the blue_sky_toys database;
[user@codingbison]$ mysql -u bluesky_root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GRANTS; +--------------------------------------------------------------------------------------------+ | Grants for bluesky_root@localhost | +--------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_root'@'localhost' IDENTIFIED BY | | PASSWORD '*C01FDB9E80BCDD3AEE748679365023271B329227' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.* TO 'bluesky_root'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | blue_sky_toys | | test | +--------------------+ 3 rows in set (0.19 sec) mysql> USE blue_sky_toys; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +-------------------------+ | Tables_in_blue_sky_toys | +-------------------------+ | employees | | inventory | | items | | stores | +-------------------------+ 4 rows in set (0.00 sec)
Second, we re-login as root and assign access to the bluesky_admin user. Once again, we provide access only for access from localhost and hence, we explicitly define user as bluesky_admin@localhost. As per the design, we provide access to only two tables employees and stores.
[user@codingbison]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> CREATE USER bluesky_admin@localhost IDENTIFIED BY "bst_password_admin"; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON blue_sky_toys.employees TO bluesky_admin@localhost; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON blue_sky_toys.stores TO bluesky_admin@localhost; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Now, let us login as bluesky_admin user and verify the privileges.
[user@codingbison]$ mysql -u bluesky_admin -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> SHOW GRANTS; +----------------------------------------------------------------------------------------+ | Grants for bluesky_admin@localhost | +----------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_admin'@'localhost' IDENTIFIED BY | | PASSWORD '*B6DDCE717F76B5143CF8EB80D93DEF9F0B4D6028' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.`employees` TO 'bluesky_admin'@'localhost' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.`stores` TO 'bluesky_admin'@'localhost' | +----------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | blue_sky_toys | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> USE blue_sky_toys; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> show tables; +-------------------------+ | Tables_in_blue_sky_toys | +-------------------------+ | employees | | stores | +-------------------------+ 2 rows in set (0.00 sec)
As we can see from above, the "SHOW GRANTS" tell us that bluesky_admin has all privileges on employees and stores table. Next, when we run "SHOW DATABASES", we find that blue_sky_toys is visible to user bluesky_admin even though bluesky_admin does not have access to all the tables. Running a "SHOW TABLES" confirms this -- as expected, this user can only see two tables: employees and stores;
Now, let us confirm that bluesky_admin has access to the two tables (employees and stores) using simple queries.
mysql> SELECT * FROM employees;
+-------------+---------------+---------------+----------+------------------------------------+
| employee_id | employee_name | date_of_birth | store_id | employee_address |
+-------------+---------------+---------------+----------+------------------------------------+
| 101 | Homer Simpson | 1955-07-23 | 1 | 742 Evergreen Terrace, Springfield |
| 103 | Peter Griffin | 1967-06-28 | 2 | 31 Spooner Street, Quahog |
| 112 | Ned Flanders | 1960-03-07 | 1 | 740 Evergreen Terrace, Springfield |
| 120 | Marge Simpson | 1965-05-21 | 1 | 742 Evergreen Terrace, Springfield |
| 121 | Lois Griffin | 1968-01-18 | 2 | 31 Spooner Street, Quahog |
| 110 | Stan Smith | 1970-12-05 | 3 | 43 Cherry Street, Langely Falls |
+-------------+---------------+---------------+----------+------------------------------------+
6 rows in set (0.00 sec)
mysql>
mysql> INSERT INTO employees VALUES(102, "Cleveland Brown", "1965-09-17", 2,
-> "300 Ocean Drive, Quahog");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM employees WHERE employee_id = 102;
+-------------+-----------------+---------------+----------+-------------------------+
| employee_id | employee_name | date_of_birth | store_id | employee_address |
+-------------+-----------------+---------------+----------+-------------------------+
| 102 | Cleveland Brown | 1965-09-17 | 2 | 300 Ocean Drive, Quahog |
+-------------+-----------------+---------------+----------+-------------------------+
1 row in set (0.34 sec)
mysql> SELECT * FROM stores LIMIT 1;
+----------+------------------------------+
| store_id | store_location |
+----------+------------------------------+
| 1 | 110 Ocean Drive, Springfield |
+----------+------------------------------+
1 row in set (0.00 sec)
Please note that we use "LIMIT 1" to print only one record for the sake of verbosity.
In the end, let us confirm that bluesky_admin has no access for the other two tables: items and inventory. As expected, running a SELECT on these tables leads to an error.
mysql> SELECT * FROM items LIMIT 1; ERROR 1142 (42000): SELECT command denied to user 'bluesky_admin'@'localhost' for table 'items' mysql> mysql> SELECT * FROM inventory LIMIT 1; ERROR 1142 (42000): SELECT command denied to user 'bluesky_admin'@'localhost' for table 'inventory' mysql>
Lastly, to create the third user, bluesky_stock, we once again login as root, create the user and grant all privileges on the remaining two tables: items and inventory.
mysql> CREATE USER bluesky_stock@localhost IDENTIFIED BY "bst_password_stock"; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON blue_sky_toys.items TO bluesky_stock@localhost; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON blue_sky_toys.inventory TO bluesky_stock@localhost; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Now, if we were to login as bluesky_stock, we would see only these two tables:
[user@codingbison]$ mysql -u bluesky_stock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.52 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW GRANTS; +----------------------------------------------------------------------------------------+ | Grants for bluesky_stock@localhost | +----------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_stock'@'localhost' IDENTIFIED BY | | PASSWORD '*4040861C2EDBE3BB071A5523B7C0AFB946908FEC' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.`inventory` TO 'bluesky_stock'@'localhost' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.`items` TO 'bluesky_stock'@'localhost' | +----------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | blue_sky_toys | | test | +--------------------+ 3 rows in set (0.07 sec) mysql> USE blue_sky_toys; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +-------------------------+ | Tables_in_blue_sky_toys | +-------------------------+ | inventory | | items | +-------------------------+ 2 rows in set (0.00 sec)
If we were to run queries on items and inventory, we would find that the user bluesky_stock has all privileges for these tables.
So far, we have been granting all privileges for either the entire database or for a table. But, sometimes, during hardening of databases, we might want to grant privileges in a more granular way.
A table level grant can be a lot more selective than our earlier examples of "GRANT ALL ON ..." queries. In fact, SQL supports several types of table-level grants. As an example, we can provide privilege only to run a SELECT query on a table; a user with such a grant is restricted from modifying the table and all she can do is merely view the records of that table.
Let us say, we were to create another user (bluesky_enduser) in our blue_sky_toys database and if this user were to be a end-user that can view existing inventory, then we can restrict the access using "GRANT SELECT ON blue_sky_toys.inventory TO bluesky_enduser@localhost;".
If we connect to mysql as bluesky_enduser, then we would see blue_sky_toys database and the table (and only this table!) inventory. All this user can do is run SELECT query; attempting to run any other query, like say DELETE or INSERT, would be denied!
MySQL supports many more privileges besides SELECT, INSERT, UPDATE, and DELETE. For a complete list, we can use "SHOW PRIVILEGES" to understand various types of privileges that can be granted to users.
mysql> SHOW PRIVILEGES; +-------------------+-------------------------+---------------------------------------------+ | Privilege | Context | Comment | +-------------------+-------------------------+---------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures| | Create | Databases,Tables,Indexes| To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary | Databases | To use CREATE TEMPORARY TABLE | | tables | | | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Event | Server Admin | To create, alter, drop and execute events | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables, | To give to other users those privileges | | | Functions,Procedures | you possess | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT | | | | privilege) | | Process | Server Admin | To view the plain text of currently | | | | executing queries | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and | | | | privileges | | Replication client| Server Admin | To ask where the slave or master servers are| | Replication slave | Server Admin | To read binary log events from the master | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, | | | | CHANGE MASTER, etc. | | Trigger | Tables | To use triggers | | Update | Tables | To update existing rows | | Usage | Server Admin | No privileges - allow connect only | +-------------------+-------------------------+---------------------------------------------+ 29 rows in set (0.00 sec)
These privileges are associated with various types of SQL queries and MySQL commands. For example, Alter privilege is associated with "ALTER" query and we can grant this to a user (e.g. bluesky_root) if the design requires the user to modify structure of tables present in the database. Similarly, drop privilege is associated with "DROP" query and we can grant this to a user (e.g. bluesky_root) so that if need be (let us say due to redesign), then the user can drop an existing table and create a new one.
Not only can we be granular in terms of tables and privilege type, we can further restrict access by providing privileges only for certain columns. When we provide a grant for a set of columns, the user cannot access anything else except for those columns.
For example, with query: "GRANT SELECT (item_name, item_price) ON blue_sky_toys.items TO bluesky_enduser@localhost;", we establish that the new user can also access items table. But all the user can do is to run "SELECT item_name, item_price FROM items". Trying to run even a "SELECT * FROM items" would be an error!
Similarly, we can provide a user with privilege to insert or update values that only for a set of columns.
However, some of the commands like DROP and DELETE have little sense for a specific column since they apply to one entire record; MySQL does not allow us to grant column-specific privileges for these commands:
mysql> GRANT DROP (item_name, item_price) ON blue_sky_toys.items TO bluesky_enduser@localhost;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '(item_name, item_price)
ON blue_sky_toys.items TO bluesky_enduser@localhost' at line 1
mysql>
mysql> GRANT DELETE (item_name, item_price) ON blue_sky_toys.items TO bluesky_enduser@localhost;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '(item_name, item_price)
ON blue_sky_toys.items TO bluesky_enduser@localhost' at line 1
To revoke an already assigned privilege, we can use the REVOKE statement. In that regard, REVOKE is almost exactly opposite to that of GRANT.
Let us say, we would like to remove the privilege granted to bluesky_root user so that root related activity can be handled by the global database server root itself. To do this, we login as root and remove the two grants provided earlier. As we revoke the grants, we also use "SHOW GRANTS" statement to see the changes in grants.
mysql> SHOW GRANTS FOR bluesky_root@localhost; +--------------------------------------------------------------------------------------------+ | Grants for bluesky_root@localhost | +--------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_root'@'localhost' IDENTIFIED BY | | PASSWORD '*C01FDB9E80BCDD3AEE748679365023271B329227' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.* TO 'bluesky_root'@'localhost' WITH GRANT OPTION | +--------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> REVOKE GRANT OPTION ON blue_sky_toys.* FROM bluesky_root@localhost; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR bluesky_root@localhost; +--------------------------------------------------------------------------------------------+ | Grants for bluesky_root@localhost | +--------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_root'@'localhost' IDENTIFIED BY | | PASSWORD '*C01FDB9E80BCDD3AEE748679365023271B329227' | | GRANT ALL PRIVILEGES ON `blue_sky_toys`.* TO 'bluesky_root'@'localhost' | +--------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> REVOKE ALL PRIVILEGES ON blue_sky_toys.* FROM bluesky_root@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR bluesky_root@localhost; +--------------------------------------------------------------------------------------------+ | Grants for bluesky_root@localhost | +--------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_root'@'localhost' IDENTIFIED BY | | PASSWORD '*C01FDB9E80BCDD3AEE748679365023271B329227' | +--------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Let us use another example to demonstrate the use of REVOKE for column-based GRANTS. For the user bluesky_enduser, let us say we now need to revoke all the privileges assigned earlier.
mysql> SHOW GRANTS FOR bluesky_enduser@localhost; +-----------------------------------------------------------------------------------------+ | Grants for bluesky_enduser@localhost | +-----------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_enduser'@'localhost' IDENTIFIED BY | | PASSWORD '*FEEACDCC919A578FE27FEB2C73B36B0767B0026F' | | GRANT SELECT ON `blue_sky_toys`.`inventory` TO 'bluesky_enduser'@'localhost' | | GRANT SELECT (item_price, item_name) ON `blue_sky_toys`.`items` TO | | 'bluesky_enduser'@'localhost' | +-----------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> REVOKE SELECT ON blue_sky_toys.inventory FROM bluesky_enduser@localhost; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR bluesky_enduser@localhost; +-----------------------------------------------------------------------------------------+ | Grants for bluesky_enduser@localhost | +-----------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_enduser'@'localhost' IDENTIFIED BY | | PASSWORD '*FEEACDCC919A578FE27FEB2C73B36B0767B0026F' | | GRANT SELECT (item_price, item_name) ON `blue_sky_toys`.`items` TO | | 'bluesky_enduser'@'localhost' | +-----------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> REVOKE SELECT (item_name, item_price) ON blue_sky_toys.items FROM bluesky_enduser@localhost; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR bluesky_enduser@localhost; +-----------------------------------------------------------------------------------------+ | Grants for bluesky_enduser@localhost | +-----------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'bluesky_enduser'@'localhost' IDENTIFIED BY | | PASSWORD '*FEEACDCC919A578FE27FEB2C73B36B0767B0026F' | +-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
As we can see, a user can have multiple grants and some of these can overlap. For example, for bluesky_enduser, it had two grants. One was SELECT on entire table (inventory) and the other was a SELECT only on two columns of this table. When we remove the SELECT grant on all columns, the other grant still remains!