Create a mysql user with limited permission to improve security¶

Suppose that you have a website and it connects to a mysql database. If the website uses the root user to access mysql, it is very dangerous.

But why is it dangerous? If your website is compromised and the root password is leaked, the cracker can do anything he wants to your mysql server. So, you should use a mysql user with limited permission instead of root to reduce damage.

CLI example¶

In the following example, we create a mysql user and only give permission to specified database rather than all databases.

# login with root
mysql -u root -p
# create new user called user1
# @'%' means that user1 can login from every ip. you can give a specific ip when necessary.
mysql> create user 'user1'@'%' identified by 'user1pwd'
# create a database called web1_db
mysql> CREATE DATABASE web1_db;
# user1 can only access web1_db
mysql> grant all privileges on web1_db.* to 'user1'@'%';
# make changes take effect immediately
mysql> flush privileges;

Some commands are prefixed with mysql> because they are typed in the mysql shell.