In MySQL database, a root user has a full access to all of the databases. However, a user (non-root) in MySQL having restrictions in the cases which may be required in different environments, in this tutorial we will learn how to create users with custom permissions.
The reason this matters is straightforward enough. Applications get pointed at MySQL with whatever credentials are handy, and the handiest credentials are usually root. It works, so nobody revisits it, and then a SQL injection in one small app has full access to every database on the server rather than the one it was supposed to touch. A user scoped to a single database turns that from a disaster into an incident.
Now we start to create a new user in the MySQL shell:
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
The two halves of that name are both doing work. ‘testuser’@’localhost’ can only connect from the server itself, and MySQL treats a different host as a different account entirely. Create ‘testuser’@’localhost’ and then try to connect from another machine and you get access denied, which looks like a password problem and is not.
That host part is a real access control, so it is worth setting deliberately rather than reaching for the wildcard because it makes the error go away.
At this point ‘testuser’ has no permissions to do anything with the databases. Even if testuser even tries to login with password, it will not be able to reach the MySQL shell.
Now we will provide access to testuser for access the databases and its tables.
mysql> GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'localhost';
Database and table indicates with asterisks respectively in this command. Above command allows edit, excute and perform all tasks in all databases and tables to the user.
Which is fine for learning how the syntax works, and is exactly the thing to avoid in production. You have just built a second root account. Replacing the first asterisk with a database name is the whole difference between a scoped account and an unscoped one, and it costs nothing to do.
When your process to allow access to the user is completed then always be sure to reload all the privileges as shown below.
mysql> FLUSH PRIVILEGES;
MySQL keeps the grant tables in memory as well as on disk. FLUSH PRIVILEGES tells it to re-read them, and skipping it is why a permission change sometimes appears to have done nothing at all.
Grant Different User Permissions:
The common possible permissions that mostly provide to user are as follows.
To provide a permission to a specific user, the syntax is as follows:
mysql> GRANT [type of permission] ON [database name].[table name] TO '[username]'@'localhost';
Most accounts need far less than people give them. An application that reads and writes rows needs SELECT, INSERT, UPDATE and DELETE. It almost never needs DROP, and giving it DROP means a bug or an injection can destroy a table rather than just corrupting rows in it. Reporting tools usually need SELECT and nothing else.
Start from nothing and add what breaks. It takes slightly longer and it produces an account that cannot do damage it was never meant to do.
Each time you update or change a permission, don’t forget to use the Flush Privileges command.
Create a new user called testuser2 for all databases and tables in a single command.
mysql> GRANT ALL ON *.* TO testuser2@localhost IDENTIFIED BY 'mypassword';
As we grant permissions to a user, we can also revoke permissions to user by following command:
mysql> REVOKE [type of permission] ON [database name].[table name] FROM '[username]'@'localhost';
REVOKE mirrors GRANT exactly, which makes it easy to reason about. Take away what you granted, in the same shape you granted it. Same rule about flushing afterwards.
As you can delete databases with DROP, you will also use DROP to delete a user as shown below:
mysql> DROP USER 'testuser'@'localhost';
Remember that the host is part of the identity here too. Dropping ‘testuser’@’localhost’ leaves ‘testuser’@’%’ alive and able to connect, so if an account was created more than once from different hosts, each one has to go separately.
One note on versions. The combined form above, where GRANT both creates the account and sets its password with IDENTIFIED BY, worked for years and was removed in MySQL 8.0. On a current server the account gets created first with CREATE USER and the privileges granted as a separate step, which is the two-command sequence this article starts with anyway. If you are following along on 8.0 and the combined version throws a syntax error, that is why.
Related reading: installing MySQL on Linux for the initial server setup, and changing the MySQL root password if you need to get back into the administrator account first.
