MySql server user permissions – Practical examples

MySql server’s user permissions - Practical examples

A busy MySql server can run multiple databases instances on the same server. It is often the case when each database needs to be managed by different application, in simple terms by different user.

MySql permissions work on per user base and they apply to whole databases or tables.

Create a new MySql database

This is a straight forward task.


mysql> CREATE DATABASE myuser_db;
Query OK, 1 row affected (0.00 sec)

Grant all permissions on a database to a new MySql user

Mysql uses “CREATE USER” command to define a new user. However, if the user specified in the “GRANT” command does not already exist, it will be created.


mysql> GRANT ALL PRIVILEGES ON myuser_db.* TO 'myuser'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Let’s discuss a little on the “GRANT” command. It is the recommended way to add permissions, specify account characteristics such as SSL connections and impose limits.

The above GRANT command sets all permissions (SELECT, INSERT, DELETE, UPDATE, etc.) on “myuser_db” database tables (.*) to user “myuser” and any host when it identifies it self with “password” password. This is not a safe practice, it is just to show the power of the GRANT Mysql command and also the weakness if used incorrectly.

GRANT only SELECT on all tables to a user


mysql> GRANT SELECT ON another_db.* to myuser;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON important_db.* TO 'myuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Check MySQL grant for current logged in user


mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------+
| Grants for myuser@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `mysql`.* TO 'myuser'@'localhost'                                                             |
| GRANT SELECT ON `important_db`.* TO 'myuser'@'localhost'                                                      |
+---------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

After logging in using the mysql client and issuing the “SHOW GRANTS” command, the server will return all permissions for current user under current circumstances (“Grants for myuser@localhost”).

Check MySql grants as administrator

As an administrator, you can have a more detailed view of all the grants given in a specific Mysql server to all users from all locations:


mysql> SELECT user, host, db, insert_priv,select_priv,delete_priv,update_priv FROM mysql.db ;
+--------+-----------+--------------+-------------+-------------+-------------+-------------+
| user   | host      | db           | insert_priv | select_priv | delete_priv | update_priv |
+--------+-----------+--------------+-------------+-------------+-------------+-------------+
| myuser | %         | myuser_db    | Y           | Y           | Y           | Y           |
| myuser | %         | mysql        | N           | Y           | N           | N           |
| myuser | %         | another_db   | N           | Y           | N           | N           |
| myuser | localhost | mysql        | N           | Y           | N           | N           |
| myuser | localhost | important_db | N           | Y           | N           | N           |
| myuser | 10.1.1.1  | important_db | N           | Y           | N           | N           |
+--------+-----------+--------------+-------------+-------------+-------------+-------------+

Based on the contents of “db” table in “mysql” database, a high level view of users@locations can be compiled and further make use of “SHOW GRANTS FOR” commands can be used for details.


mysql> SHOW GRANTS FOR ‘myuser’@’10.1.1.1’;
+---------------------------------------------------------+
| Grants for myuser@10.1.1.1                              |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'10.1.1.1'               |
| GRANT SELECT ON `important_db`.* TO 'myuser'@'10.1.1.1' |
+---------------------------------------------------------+
mysql> SHOW GRANTS FOR myuser@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `mysql`.* TO 'myuser'@'%'                                                             |
| GRANT SELECT ON `another_db`.* TO 'myuser'@'%'                                                        |
| GRANT ALL PRIVILEGES ON `myuser_db`.* TO 'myuser'@'%'                                                 |
+-------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

One thing to note is that you have to be careful with the “%” character in MySql. It is used as a wildcard and in the GRANT context it can be dangerous as it can allow a user to connect from any remote host or any database or any table. It is dangerous to use the “GRANT” on a user without specifying login circumstances (‘user’@‘location’).

For further reading, see MySql GRANT manual at: hxxp://dev.mysql.com/doc/refman/5.1/en/grant.html

 

You can actually try those database settings on our platform in few minutes utilizing our PCS (Private Cloud Solution) which allows you to have VPSie(s) on a private network – NAT – Port forward – traffic control for inbound and outbound – multiple gateway IPs which you could use for the load-balancing and failover.