Introduction
MySQL is open-source database management software that helps users store, organize and retrieve data. A wide range of options is available for granting the appropriate permissions to specific users on tables and databases. This tutorial gives a short overview of a few of the many options available.
How to create a new user
You have probably already made changes in MySQL as the root user, with full access to every database. However, in cases where you need to set up additional restrictions, there are ways to create users with custom permissions.
Let us begin by creating a new user in the MySQL shell:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';Note: when we add users in the MySQL shell during this tutorial, we will state that the user host is localhost and not the IP address of the server. localhost is a host name that means "this computer". MySQL treats this particular host name in a special way: when a user with this host connects to MySQL, it will try to connect to the local server using a Unix socket file. As a general rule, therefore, you can use localhost if you plan to connect to your server over SSH or to run the local mysql client to connect to the local MySQL server.
At this stage, username has no permission and can do nothing on the databases. Indeed, even if username tries to connect (with the password password), it will not be able to reach the MySQL shell.
The first thing to do, therefore, is to give the user access to the information it needs.
GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';The asterisks in this command refer to the database and to the table (respectively) that the user can access. This particular command allows the user to read, modify, execute and perform any task on all databases and tables.
Note that, in this example, we are granting username full root access to everything in our database. While this is useful for explaining certain MySQL concepts, it may be impractical in most use cases and it exposes your database to significant security risks.
Once you have finished setting the permissions you want for your new users, always make sure to reload all privileges.
FLUSH PRIVILEGES;Your changes have now taken effect.
How to grant different permissions to users
Below is a short list of the other commonly used permissions that you can grant to users.
- ALL PRIVILEGES: as seen previously, this gives the MySQL user full access to a designated database (or global access to the whole system if no database is selected)
- CREATE: allows new tables or databases to be created
- DROP: allows tables or databases to be deleted
- DELETE: allows rows to be deleted from tables
- INSERT: allows rows to be inserted into tables
- SELECT: allows the SELECT command to be used to read databases
- UPDATE: allows the rows of a table to be updated
- GRANT OPTION: allows the privileges of other users to be granted or removed
To give a permission to a specific user, you can use the following command:
GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';If you want to give the user access to any of the databases or tables, make sure to replace the name of the database or of the table with an asterisk (*).
Every time you update or change a permission, make sure to use the Flush Privileges command.
To revoke a permission, the structure is almost identical to the one used to grant it:
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';Note that, when you revoke permissions, you must use the FROM syntax in place of the TO used to grant permissions.
You can check the permissions currently granted to a user by running the following command:
SHOW GRANTS FOR 'username'@'localhost';In the same way that you can delete databases with DROP, you can use DROP to delete a user entirely:
DROP USER 'username'@'localhost';To test your new user, log out by entering:
quitand log back in using the following command in the terminal:
mysql -u username -pConclusion
Having completed this tutorial, you should have a better idea of how to add new users and grant them different permissions in a MySQL database. From here, you can go on exploring and experimenting with the various permission settings that exist for your database, or learn more about some higher-level MySQL configurations.