Introduction
MySQL is an open-source relational database management system. It is the most popular open-source database in the world.
It is commonly deployed as part of the LAMP stack (which stands for Linux, Apache, MySQL, and PHP) .
This article outlines how to create a new MySQL user and grant them the permissions needed to perform a variety of actions.
Prerequisites
In order to follow along with this article, you’ll need access to a MySQL database. This article assumes that this database is installed on a AWS instance running CentOS 7, though the principles it outlines should be applicable regardless of how you access your database.
If you don’t have access to a MySQL database and would like to set one up yourself, you can follow one of our article on How To Install MySQL. Again, regardless of your server’s underlying operating system, the methods for creating a new MySQL user and granting them permissions will generally be the same.
Creating a New User
Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server. The root has complete control over every database, table, user, and so on.
In my CetnOS systems running MySQL 5.7, the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. To access mysql with root user and password you need execute bellow commend.
$ mysql -u root -p
Once you have access to the MySQL prompt, you can create a new user with a CREATE USER statement. Follow this general syntax bellow:
CREATE USER 'username' IDENTIFIED BY 'password';
Replace username and password with a username and password of your choice.
Alternatively, you can set up a user by specifying the machine hosting the database.
- If you are working on the machine with MySQL, use username@localhost to define the user.
- If you are connecting remotely, use username@ip_address, and replace ip_address with the actual address of the remote system hosting MySQL.
Therefore, the user create commend will be:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
OR
CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';
You can also create a user that can connect from any where with this command:
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
Granting a User Permissions
After create a new account, make sure you have set the permissions for the user.
After create a new account, make sure you have set the permissions for the user.
Permissions are actions that the user is allowed to perform in the database. Depending on how much authority you want your user to have, you can grant them one, several or all of the following privileges:
All Privileges: The user account has full access to the database
Insert: The user can insert rows into tables
Delete: The user can remove rows from tables
Create: The user can create entirely new tables and databases
Drop: The user can drop (remove) entire tables and databases
Select: The user gets access to the select command, to read the information in the databases
Update: The user can update table rows
Grant Option: The user can modify other user account privileges.
The general syntax for granting user privileges is as follows:
GRANT PRIVILEGE ON database.table TO 'user'@'host';
For example, to grant insert privileges to a MySQL user you would run the command:
GRANT PRIVILEGE ON *.* TO 'user'@'host';
You can replace the privileges label for specific user according you need. If you want to limit the user's access to a database.table or a database you can set the access by bellow commend :
For specific database
GRANT INSERT *database_name.* TO 'username'@'localhost';
or for specific database table
GRANT INSERT *database_name.table_name* TO 'username'@'localhost';
Revoke Privileges MySQL User Account
To revoke a specific user privileges you need to use the REVOKE command. It works similar to the GRANT command, the syntax bellow :
REVOKE permission_type ON database.table TO 'username'@'localhost';
Remove a MySQL User Account
To delete a MySQL user you need to run bellow command syntex:
DROP USER 'username'@'localhost';
Conclusion
Hopefully you should now be able to create, modify, delete users and grant permissions in a MySQL database.
