Understanding MySQL/MariaDB Users And Privileges: Part 2

Previously we have looked at how user accounts work the MySQL or MariaDB database systems, and how you can both create and remove them. This time we’ll be looking at making those users useful by ensuring that they have privileges to actually make changes to your database.

Managing Privileges

First, let’s look at the commands you’ll need to be using to manage these privileges. Just as last time, you’ll need to log into your database client with the root user to run these commands:
“`
GRANT privilege_type ON database_name.table_name TO ‘user’@’host’;
“`
The first part is the GRANT command, used to give a privilege to a user. We will explain the “privilege_type” a little later, but hopefully, you’ll be familiar with the ‘user’@’host’ way of referencing a user account from last time. The “database_name.table_name” is the part we are interested in for the moment. Each MySQL or MariaDB server is capable of handling multiple databases with each database made up of tables of data. The privilege system is quite fine-grained allowing to specify user account permissions down to specific tables meaning that you can ensure that user accounts can only view or manipulate the exact data you want them to. A wildcard, in this case, the asterisk (*), can be used to allow you to specify all databases or tables.
“`
REVOKE privilege_type ON database_name.table_name TO ‘user’@’host’;
“`
Next, we have the REVOKE command, which, as you can probably guess, removes a privilege from the user account. The command format is the same as for the GRANT command.
To view the privileges for a specific user we can use the SHOW GRANTS command as follows:
“`
SHOW GRANTS FOR ‘user’@’host’;
“`
So let’s have a look at some of these privilege types:
* CREATE This allows a user account to create a new table or database.
* DROP   This allows a user account to delete a table or database.
* INSERT This allows a user account to add a new row to a table.
* UPDATE This allows a user account to change table row data.
* DELETE This allows a user to delete rows from a table.
* SELECT This allows a user to view data in the database.
* ALL This gives a user account all privileges on a specified
database, table or with nothing specified the system.

Managing Other Privilege Options

This isn’t an exhaustive list of the privilege types, but it does cover a lot of the commonly used ones that you might want to use when adding user accounts to a database. For a complete list take a look at Table 13.3 in the MySQL manual.
So let’s look at this privilege system in action:
“`
GRANT ALL ON *.* TO ‘user’@’host’;
“`
This command would grant all privileges on the database system to the user account ‘user’@’host’. This isn’t generally a good idea as you already have the root account with those permissions, and there is little need for a second account with the same privilege levels.
“`
GRANT ALL ON my_database.* TO ‘user’@’host’;
“`
The above command grants the user account ‘user’@’host’ all privileges on the database my_database.
“`
GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.* TO ‘user’@’host’;
“`
In this example, the command allows the user account ‘user’@’host’ to use the SELECT, INSERT, UPDATE and DELETE commands on all of the tables in the my_database database.
“`
GRANT SELECT ON old_database.table_4 TO ‘user’@’host’;
“`
Finally, the above command allows ‘user’@’host’ to use the SELECT command on the table table_4 of the database old_database.
Once you have created your users and assigned their privileges, the last step is to reload all the user privileges so that they can use the database. This can be done with the following command:
“`
FLUSH PRIVILEGES;
“`That wraps up our guide to working with users with your MySQL or MariaDB databases. You should now have a reasonable understanding of how the users and privileges work and should be able to manage the users for your databases to meet your requirements.