Adding users and assigning access rights

In this recipe, we will learn how to add new users to the MySQL database server. MySQL provides very flexible and granular user management options. We can create users with full access to an entire database or limit a user to simply read the data from a single database. Again, we will be using queries to create users and grant them access rights. You are free to use any tool of your choice.

Getting ready

You will need a MySQL user account with administrative privileges. You can use the MySQL root account.

How to do it…

Follow these steps to add users to MySQL database server and assign access rights:

1. Open the MySQL shell with the following command. Enter the password for the admin account when prompted:

   $ mysql -u root -p
   

2. From the MySQL shell, use the following command to add a new user to MySQL:

   mysql> create user ‘dbuser’@’localhost’ identified by ‘password’;
   

3. You can check the user account with the following command:

   mysql> select user, host, password from mysql.user where user = ‘dbuser’;
   

   
4. Next, add some privileges to this user account:

   mysql> grant all privileges on *.* to ‘dbuser’@’localhost’ with grant option;
   

5. Verify the privileges for the account as follows:

   mysql> show grants for ‘dbuser’@’localhost’
   

   
6. Finally, exit the MySQL shell and try to log in with the new user account. You should log in successfully:

   mysql> exit
   $ mysql -u dbuser -p
   

How it works…

MySQL uses the same database structure to store user account information. It contains a hidden database named MySQL that contains all MySQL settings along with user accounts. The statements create user and grant work as a wrapper around common insert statements and make it easy to add new users to the system.

In the preceding example, we created a new user with the name dbuser. This user is allowed to log in only from localhost and requires a password to log in to the MySQL server. You can skip the identified by ‘password’ part to create a user without a password, but of course, it’s not recommended.

To allow a user to log in from any system, you need to set the host part to a %, as follows:

mysql> create user ‘dbuser’@’%’ identified by ‘password’;

You can also limit access from a specific host by specifying its FQDN or IP address:

mysql> create user ‘dbuser’@’host1.example.com’ identified by ‘password’;

Or

mysql> create user ‘dbuser’@’10.0.2.51’ identified by ‘password’;

Note that if you have an anonymous user account on MySQL, then a user created with username’@’% will not be able to log in through localhost. You will need to add a separate entry with username’@’localhost.

Next, we give some privileges to this user account using a grant statement. The preceding example gives all privileges on all databases to the user account dbuser. To limit the database, change the database part to dbname.*:

mysql> grant all privileges on dbname.* to ‘dbuser’@’localhost’ with grant option;

To limit privileges to certain tasks, mention specific privileges in a grant statement:

mysql> grant select, insert, update, delete, create
 -> on dbname.* to ‘dbuser’@’localhost’;

The preceding statement will grant select, insert, update, delete, and create privileges on any table under the dbname database.

There’s more…

Similar to preceding add user example, other user management tasks can be performed with SQL queries as follows:

Removing user accounts

You can easily remove a user account with the drop statement, as follows:

mysql> drop user ‘dbuser’@’localhost’;

Setting resource limits

MySQL allows setting limits on inpidual accounts:

mysql> grant all on dbname.* to ‘dbuser’@’localhost’
 -> with max_queries_per_hour 20
 -> max_updates_per_hour 10
 -> max_connections_per_hour 5
 -> max_user_connections 2;

See also

• MySQL user account management at https://dev.mysql.com/doc/refman/5.6/en/user-account-management.html