Introduce
MySQL is an open source database management system that helps users store, organize, and then retrieve data. It has a variety of options available to the user to manipulate tables and databases. This article guides user creation and authorization in MySQL.
As soon as you start using the MySQL database, you will be provided with a username and password. These login credentials will give you “root access”. A root user has full access to the databases and tables in the databases. But over time or manipulating database production, for example, you will need to grant the database access to a user without giving them full control.
How to create a new user in MySQL?
To create a new user, follow the steps below:
- Access to mysql
- Execute the command to create the user:
1 2 | CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; |
newuser: the user name
password: the user’s password
At this point, the new user does not have permission to do anything with the database. In fact, even if the new user tries to login, they will not be able to access the MySQL shell.
Therefore, the first thing to do is to give the user access to the information they need
- Assign permissions to users
1 2 | GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; |
The * above corresponds to the database and table that the user can access – in particular, this command allows the user to add, edit, and delete work on all tables in the database.
- For changes to be made immediately, use the following command:
1 2 | FLUSH PRIVILEGES; |
Thus, with the steps above, your newly created user has full authority as the root user
Several permissions you can assign to users in MySQL
Below is a list of commonly used commands to assign authority to a user:
- ALL PRIVILEGES – Allows MySQL user to have full authority on databases (or some set up db)
- CREATE – Allows a user to create new tables or databases
- DROP – Allows you to delete tables or databases
- DELETE – Allows deletion of data records in the tables table
- INSERT – Allows adding new records to the database table
- SELECT – Allows you to use the Select command to search for data
- UPDATE – Allows updating csdl
- GRANT OPTION – Allows you to assign or delete the rights of other users.
MySQL allows to assign permissions to users with a simple command line:
1 2 | GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost'; |
If you want to allow users to access all databases or all tables, use an * instead of the database name or table.
Every time you update or change permissions, use the Flush Privileges command to ensure the changes take effect.
1 2 | FLUSH PRIVILEGES; |
To apply multiple types of permissions, separate them with commas. For example, you can assign CREATE and SELECT permissions to a user:
1 2 | GRANT CREATE, SELECT ON database_name.table_name TO 'username'@'localhost'; |
If you need to revoke user rights, use the command REVOKE:
1 2 | REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost'; |
For example, to revoke all permissions for a user:
1 2 | REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost'; |
Or you can also delete the user:
1 2 | DROP USER 'username'@'localhost'; |
You can check that the newly created user is successful and able to log in, first perform a logout by:
1 2 | quit |
and login using the command
1 2 | mysql -u[username] -p[password] |
NOTE: Remember, to exercise these permissions you need root privileges. Also, be sure to execute FLUSH PRIVILEGES after making any permission changes.
How to display permissions of a user in MySQL?
To know what permissions you have to assign to a MySQL, you can use the SHOW GRANTS command:
1 2 | SHOW GRANTS FOR 'username'@'localhost'; |