Introduction
MySQL is an open source database management system that utilizes Structured Query Language (SQL) to add, access, and manage content in a database.
MySQL operates on virtually all platforms and is mostly associated with online publishing and web applications. SQL itself is renowned for its reliability, quick processing, and inimitable flexibility as well as ease of use.
Being one of the best database systems, MySQL offers an array of options to grant new users permissions to store, organize or retrieve data. This tutorial will show you how to generate new users in MySQL and assign users specific MySQL privileges.
Before you Begin
For this tutorial to flow seamlessly, you require the following:
- MySQL deployed on a server through a hosting service that supports MySQL hosting. Alternatively, you should have MariaDB deployed on your server.
Step 1 – Creating A New User
If you are familiar with MySQL, then you know how to perform basic editing in a MySQL database as a root user with unlimited access to the databases. However, in some cases, you may want to generate user accounts with more restrictions, and this is where this tutorial comes in handy.
First, execute the command below to log in to the MySQL command line.
$ sudo mysql -u root -p
Type your MySQL credentials and press ENTER to log in.
Note: If have not configured any password for the MySQL database root user, eliminate the -p flag from the command above.
To generate a new user in MySQL, execute the command below:
mysql>
CREATEUSER'hostuser'@'localhost'IDENTIFIEDBY'user_password';
Note: Remember to replace hostuser with your ideal username and user_password with a password that is strong enough, but easy to recall.
The ‘hostuser’@’localhost‘ in this command means localhost is the host for the new user and not the IP address for your server. From this, it can be deduced that the new user will only connect to your MySQL server from your localhost.
If you want the user to connect to your database server from a different host you can replace ‘localhost’ with your server’s IP address. For instance, if you want to give access from your server with IP address 127.8.0.5, then you would execute the command:
mysql>
CREATEUSER'hostuser'@'%'IDENTIFIEDBY'user_password';
In addition, if you want to give access from any other host, then replace ‘localhost’ with the ‘%’ wildcard to the command:
mysql>
CREATEUSER'hostuser'@'%'IDENTIFIEDBY'user_password';
By now, you have a new user called hostuser, but you have granted no permission to this user. If hostuser tries to log in they cannot access the MySQL command line.
Step 2 – Granting The New User Permissions
As aforementioned, MySQL gives you an array of permissions that you can give to the newly created user. The list is long and you can see all the options here. In this tutorial, we’ll highlight some of the most frequently used permissions. These include:
- ALL PRIVILEGES: As the name suggest, this command gives users unlimited access to a specific database. This means the new user has all the privileges to perform and execute all functions within the designated database.
- CREATE: The user has permission to create tables and databases.
- DELETE: The user can expunge rows from the designated table.
- DROP: The user can drop tables and databases
- INSERT: The user can add rows to a specific table
- SELECT: User has the permission to read the specified database
- UPDATE: User can update the rows in a table.
- GRANT OPTION: User can give or remove the privileges of other users
The basic syntax or framework of the command used to grant new users with privileges takes the form:
mysql>
GRANT type_of_permission ON database_name.table_name TO'hostuser'@'localhost';
If you are giving a user multiple permission, the command would take the form:
mysql>
GRANT permission1, permission2 ON database_name.table_name TO'hostuser'@'localhost';
On the other hand, if you are giving a new user access to any table or any database, replace the table or database name with an asterisk (*).
Examples
To grant ‘hostuser’ all privileges, execute the command:
mysql>
GRANT ALL PRIVILEGESON database_name.* TO'hostuser'@'localhost';
Next, to grant ‘hostuser’ privileges over all your databases, run the command:
mysql>
GRANT ALL PRIVILEGESON *.* TO'hostuser'@'localhost';
If you want to grant ‘hostuser’ privileges over a designated table with a specific database, then run the command:
mysql>
GRANT ALL PRIVILEGESON database_name.table_name TO'hostuser'@'localhost';
Then to grant ‘hostuser’ multiple permissions over a designated database, execute the command:
mysql>
GRANTSELECT, INSERT, DELETEON database_name.* TO hostuser@'localhost';
Once you grant all the required privileges, run the command below to flush the changes and enable the permissions to be in effect:
mysql>
FLUSHPRIVILEGES;
Step 3 – Managing User Privileges
Displaying User’s Privileges
If you want to know the permission assigned to ‘hostuser’, run the command below:
mysql>
SHOWGRANTSFOR'hostuser'@'localhost';
This will give you an output similar to the one below:
+---------------------------------------------------------------------------+ | Grants for hostuser'@localhost | +---------------------------------------------------------------------------+ | GRANTUSAGEON *.* TO'hostuser'@'localhost' | | GRANT ALL PRIVILEGESON`database_name`.* TO'hostuser'@'localhost' | +---------------------------------------------------------------------------+ 2rowsinset (0.00 sec)
Revoking Privileges
The command used to revoke a single or multiple permissions given to ‘hostuser’ takes the following syntax:
mysql>
REVOKE type_of_permission ON database_name.table_name FROM'hostuser'@'localhost';
For instance, to nullify all privileges given to ‘hostuser’ over a designated database, execute the command below:
mysql>
REVOKE ALL PRIVILEGESON database_name.* TO'database_user'@'localhost';
Removing ‘hostuser’
If for any reason you want to remove the new user, run the command below:
mysql>
DROPUSER'hostuser'@'localhost'
This command will remove ‘hostuser’ together with all its permissions.
Conclusion
That is it! You have learned how to come up with new MySQL database users account and assign specific permissions. The tutorial has covered a portion of the privileges you can assign to a newly created user. There are numerous privileges options that can be assigned to a MySQL user. Explore these options and feel at liberty to assign them accordingly.
