Do you think IIT Guwahati certified course can help you in your career?
Introduction
Privileges are some of the special powers which are assigned to the users to operate the database, In this article, we will learn about MySQL Manage Roles which are assigned to the users that require the same set of privileges. In MySQL, privileges are assigned to respective users according to the requirements.
If we wish to modify these privileges for the user, then we need to do it individually for every user, which is a redundant task. MySQL came up with an object called 'role,' which helps modify the privileges more efficiently. Let us see how we use this new concept to make our job easy.
My SQL Role
The MySQL Role is simply a collection of privileges, which we can assign to the users. We can grant or revoke privileges to the role just like we do in granting or revoking user accounts.
Follow these steps if we wish to provide numerous users with the same privileges:
Create a new role first.
Give the role privileges.
Assign the role to the users.
We only need to alter the privileges of the granted role if we want to change the privileges of the users. All users who have been given the position will be affected by the modifications.
The MySQL role eases our task and cuts off the redundant part.
Let us look at some of the examples of MySQL roles to understand this better.
My SQL Role Example
At the very first, we need to create a database named ‘BMS’ inside which we will create a table and insert the values into it. Let us see how we can do it.
Creating a Database
CREATE DATABASE BMS;
Here, we created the database, now we need to select it using the following command.
USE BMS;
Creating a table
Now, we will create a table named Banking and insert some values into it with the help of the following command.
CREATE TABLE Banking(
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL,
email VARCHAR(255)
);
Let us now insert values inside it.
INSERT INTO Banking(first_name,last_name,phone,email)
We are given a database named 'BMS' which stands for the Banking Management system. This database will be used by the manager account, which will have all the privileges, the client's account, which can only read the data and, the analyst account who can read/modify the database.
In this case, we have three different types of accounts, and all of them require additional privileges, as discussed above. We may have multiple managers and user accounts, so we need to create a set of roles and assign the roles to accounts accordingly.
Creating roles
Let us now create three roles according to our requirements with the help of the following commands.
CREATE ROLE
BMS_MANAGER,
BMS_CLIENT,
BMS_ANALYST;
Since we have created the roles, we now need to grant privileges to them.
We do it with the help of the ‘GRANT’ command of MySQL.
Granting Privileges
The manager needed all the database privileges, so we granted them all. We are granting privileges to the BMS_MANAGER role with the help of the following query.
GRANT ALL
ON BMS.*
TO BMS_MANAGER;
2. Clients need only read access, so we have granted that privilege. We are granting privileges to the BMS_CLIENT role with the help of the following commands.
GRANT SELECT
ON BMS.*
TO BMS_CLIENT;
3. Analysts need the read and modify privileges, so we have granted them. We are now granting privileges to the BMS_ANALYST role.
GRANT INSERT, UPDATE, DELETE
ON BMS.*
TO BMS_ANALYST;
Now, we need to assign these roles to specific users. Suppose we have one account for the manager, two accounts for the clients, and one for the analyst.
Creating User accounts
Let us create these accounts.
CREATE USER BMS_MANAGER1@LOCALHOST IDENTIFIED BY ‘Secure 1234 ’;
CREATE USER BMS_CLIENT1@LOCALHOST IDENTIFIED BY ‘Secure 1235’;
CREATE USER BMS_CLIENT2@LOCALHOST IDENTIFIED BY ‘Secure 1237’;
CREATE USER BMS_ANALYST1@LOCALHOST IDENTIFIED BY ‘Secure 2235’;
Now we will assign the role to the users.
Assigning the roles to the users
We will again use the GRANT command for doing it.
GRANT BMS_MANAGER
TO BMS_MANAGER1@LOCALHOST;
With the help of this query, we granted the role BMS_MANAGER to the user account named BMS_MANAGER1.
GRANT BMS_CLIENT
TO BMS_CLIENT1@LOCALHOST;
GRANT BMS_CLIENT
TO BMS_CLIENT1@LOCALHOST;
With the help of the above query, we granted the role BMS_CLIENT to the user account named BMS_CLIENT1 and BMS_CLIENT2.
GRANT BMS_ANALYST
TO BMS_ANALYST1@LOCALHOST;
With the help of this query, we granted the role BMS_ANALYST to the user account named BMS_ANALYST1.
We can verify whether the user account has been granted the privileges according to our requirements or not with the help of the 'SHOW GRANTS' command.
It is discussed in the following section.
MySQL SHOW GRANTS
As the name suggests, this command is used in MySQL to display the already assigned privileges to the user account.
The syntax for this command is given below.
SHOW GRANTS
FOR {USER | ROLE}
With the above syntax, we can either display the granted privileges of a user or a role.
If we omit the for clause, then the command will display the current user's privileges.
Let us see how we can use the SHOW GRANTS command to display the privileges of the BMS_ANALYST1 user.
SHOW GRANTS
FOR BMS_ANALYST1@localhost;
This query will display the privileges of the BMS_ANALYST1 user account.
Note - There are several other operations which we can perform on the MySQL roles,which is covered in MySQL Manage role|Part-2 .
With learning this, we come to the end of this blog and now let us see some of the FAQs associated with this topic.
FAQs
1. What are the kinds of privileges we can grant to the user accounts? There are several kinds of MySQL privileges: SELECT, DELETE, INSERT, and UPDATE. We can grant that to the user accounts.
2. What if we only use the SHOW GRANTS command without using any clause. It will display the privileges of the current user.
3. How can we display the privileges assigned to a Role in MySQL? We can do it with the help of the SHOW GRANTS command along with the 'FOR' clause.'
SHOW GRANTS
FOR ROLE_NAME;
Let us now summarise our learning and see the key takeaways.
Key takeaways
In this blog, we saw how to create a role to cut off the extra time to grant privileges to different user accounts individually. In the beginning, we learned what MySQL roles are and how useful they are. The syntax followed it.
Then, we discussed an example of a Banking management system database used by three different profiles of managers, clients, and analysts. These three profiles required additional privileges, so we created three different roles and assigned them to the required user accounts according to their profile.
Towards the end of this blog, we saw how we could display the user's privileges with the help of the SHOW GRANTS command. This marks the end of this blog.
310+ registered 
