In this tutorial, you’ll learn how to create a user in MySQL, how to create roles, how to assign roles to the user, how to revoke roles from a user and how to check assigned roles to a particular user.
Introduction to MySQL Roles
MySQL helps you to create roles according to the privileges you want to grant and then you can assign those roles to different users. Up until MySQL 5.7 version, you could just directly assign the permissions to users and revoke those if necessary, which used to create a lot of confusion as there were a lot of users with different requirements.
MySQL 8.0 introduced roles, where you can first create roles pertaining to the requirements, for example, you want to assign a user with read-only access, other users can be developers or database administrators which in turn means that they will have all the privileges and so on. There are a set of privileges you can assign to the corresponding roles in MySQL.
What Privileges Can A User Acquire?
There are several privileges that a user can attain, the privileges are listed below:
- SELECT: This privilege is for read-only, if a user gets SELECT privilege then they will only be able to view databases.
- CREATE: This privilege is for creating tables, if a user gets CREATE privilege then they will be able to create a new table.
- INSERT: This privilege is for inserting into the table, if the user gets INSERT privilege then they will be able to insert new records into the table.
- DELETE: This privilege is for deleting from the table, if the user gets DELETE privilege then they will be able to delete records from the table.
- DROP: This privilege is for dropping tables, if a user gets DROP privilege then they’ll be able to drop the table.
- UPDATE: This privilege is for updating tables, if a user gets the UPDATE privilege then they’ll be able to update the existing records.
- ALTER: This privilege is for altering table definition, if the user gets ALTER privilege then they will be able to alter the table definition.
- ALL: This privilege is for all privileges, if the user gets ALL privileges then they will acquire all the above-mentioned privileges.
Creating Users in MySQL
You can create multiple users on a MySQL server. For those users, you can:
- Password protect access to the database
- Assign roles
- Restrict the number of queries in a particular session
For more details, you can also refer to the official page which is listed at the end of the tutorial.
Also read: MySQL User Creation, Display, Delete – Complete Guide
Syntax:
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';Code language: JavaScript (javascript)Username: You can give the name which suits your naming convention, such as name_desgination, desgnation_name, etc.
Hostname: Hostname is the name of the server host, when we connect to MySQL server, typically we name it as localhost.
Password: You can write any password, just remember to save the password somewhere so that you’d be able to use it when you need it.
Here are a few examples of how to create users:
CREATE USER 'reader'@'localhost' IDENTIFIED BY 'pass';
CREATE USER 'rw_user'@'localhost' IDENTIFIED BY 'pass';
CREATE USER 'default_user'@'localhost' REQUIRE NONE;Code language: SQL (Structured Query Language) (sql)If you don’t want a user to have a password to access MySQL you can simply use REQUIRE NONE just like in the above example, instead of using IDENTIFIED BY.
How to Create a Role in MySQL?
MySQL roles are the collection of privileges that are then assigned to users.
Syntax:
CREATE ROLE rolename;Code language: SQL (Structured Query Language) (sql)Example:
CREATE ROLE 'Developer', 'DBA', 'viewer';Code language: SQL (Structured Query Language) (sql)Here 3 roles have been created Developer, DBA(Database Administrator), and viewer. Likewise, you can create several roles, you can also create roles like a writer, reader-writer, and more as per your needs.
How to Provide Privileges?
GRANT command is used to assign privileges to the role or users.
Syntax
GRANT [privilege] ON [database.table] TO [user];Code language: SQL (Structured Query Language) (sql)You can provide privileges as shown in the below command, we will provide all the privileges to DBA and Developer as both will have full access over the Database, the viewer on the other hand will only have the SELECT privilege.
GRANT ALL ON db.* TO 'DBA';
GRANT ALL ON work_database.* TO 'Developer';
GRANT SELECT ON db.* TO 'viewer';Code language: SQL (Structured Query Language) (sql)How to Assign Roles to Users?
Now, you’ve learned how to create users and how to create roles in MySQL. Now, let’s understand how we can assign roles to the users in MySQL. GRANT command is used to assign roles to the users.
GRANT 'Developer' TO 'Mike_Dev'@'localhost';
GRANT 'DBA' TO 'Simran_DBA'@'localhost';
GRANT 'viewer' TO 'Rama_Intern'@'localhost';Code language: SQL (Structured Query Language) (sql)How to Check Roles Assigned to Users in MySQL?
You can Check the roles by using the command SHOW GRANTS FOR as shown in the below example.
SHOW GRANTS FOR 'Simran_DBA'@'localhost';Code language: SQL (Structured Query Language) (sql)
In the above example, You can see the table constituting privileges assigned to the user called Simran DBA. Likewise, you can also check other users’ privileges as well.
How to Revoke Roles from a User?
You can revoke roles from users by using the REVOKE command.
Syntax:
REVOKE [role] FROM [user]@[host];Code language: SQL (Structured Query Language) (sql)For example, let’s revoke the DBA role from Simran and then check what privileges are left with that user.
In the above example, you can see we used REVOKE ‘DBA’ FROM ‘Simran_DBA’@’localhost’;
This revoked the DBA role from that user and now the user does not have any privileges as shown in the above GRANT table.
Useful Resources
MySQL Roles Official Document: https://dev.mysql.com/doc/refman/8.0/en/roles.html
MySQL Privileges Official Document: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
MySQL Users Official Document: https://dev.mysql.com/doc/refman/8.0/en/create-user.html