MySQL Manage Roles | Part - 2

Setting active roles

A user account can modify the current user's effective privileges within the current session by specifying which granted roles are active.

If we wish to have no active roles, we can execute the following query.

On the contrary, If we wish to set all the granted roles active, we can use the following command.

We can use the following query to set active roles to the default roles made by executing the SET DEFAULT ROLE command.

If we wish to take active roles, particularly to some of them, we can write their name with the necessary commands in the following manner.

This was all about setting active roles. In the following section, we will learn how to revoke any privilege from the role.

Revoking Privilege From A Role

It may sometimes happen that the admin needs to remove a particular privilege from the role to meet the task's requirements. So Instead of creating a new role with non-omitted privileges, we can directly revoke the privilege from the role in MySQL.

Suppose we need to revoke the Insert and Delete privileges from the BMS_ANALYST role, then we can execute the following query.

And not only this we can regrant the privileges if needed in the following manner.

Let us now see how we can remove a role in MySQL.

Remove A Role

MySQL also offers a way to remove a role from it with the help of DROP Command. We can do it in the following syntax.

For example, if we wish to remove the BMS_ANALYST role, it could be done with the following command.

This was how we could remove a role. Let us now see how we can copy privileges from other user accounts.

Copy Privileges From User Accounts

We may need some of the privileges for a newly created user account. To accomplish this task, firstly, we need to create a new user, and then we can execute the following commands to copy privileges.

The above command will copy all the privileges acquired by the user ‘bms_analyst1’ to the new user ‘bms_analyst2’.

In this manner, we end this blog and will now discuss some of the frequently asked questions related to this topic.0

FAQs

1.  Is it possible to regrant the revoked privileges to the roles?
    Yes! We need to reuse the 'GRANT' command to regrant privileges to the roles.
2. How can we create a new user in MySQL?
   We can create a new user in MySQL with the help of the following query.
   CREATE USER USER_NAME@LOCALHOST; 
3. Is it necessary to set default roles?
   No! It is not necessary to do it, but if you want to be able to access the database for the user, you need to set default roles.
4. How we can show already assigned privileges?
   We can use the SHOW PRIVILEGES command in MySQL to show the already assigned privileges of the user account.
5. What are the various operation that can be performed on MySQL Roles?
   We may perform various operations such as setting default roles, active roles, revoking a role or privilege, and copying privileges from user accounts

Let us now summarize our learnings!

Key Takeaways

In this article, we have extensively discussed MySQL Manage roles and their implementation in MySQL. We learned so many useful operations that we can perform after creating a role and implementing them. We also answered some of the frequently asked questions related to this topic. 

Recommended Readings:

• MySQL Case Statement

We hope that this blog has helped you enhance your knowledge regarding MySQL Manage roles and if you would like to learn more, check out our articles on Coding Ninjas Studio. Do upvote our blog to help other ninjas grow. Happy Coding!