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.
Now we start to create a new user in the MySQL shell:
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';
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.
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;
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';
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';
As you can delete databases with DROP, you will also use DROP to delete a user as shown below:
mysql> DROP USER 'testuser'@'localhost';
