How to create and Drop Role in MySQL Server - MySQL DBA Tutorial

What is Role in MySQL, How to create Role and How to drop Role in MySQL Server

Roles in MySQL Server really helps to group the permissions to single object ( Role) and then you can assign role to user instead of you create each user individually and then grant permissions.

Syntax:

MySQL> Create ROLE ROLE_Name;

Example : 
Let's say we want to create Select role with  name "Role_Select" and then Grant Show databases and Select permission on all the tables in TechBrothers Database. Once the role is created we would like to assign user TB and TB1 to it.

MySQL> Create Role Role_Select; 

Grant Permissions to Role " Role_Select"

MySQL> GRANT SHOW DATABASES ON *.* to Role_Select;


MariaDB > GRANT ALL ON TechBrothers.* to Role_Select; 

Assign  Role_Select  role to user TB.

MySQL>  GRANT Role_Select To 'TB'@'localhost';

Grant Role_Select to user TB1.

MySQL>  GRANT Role_Select To 'TB1'@'localhost'; 

Now once the user TB or TB1 will login to MySQL Server, they can use this role. Once the user login , they can check which role they are using by using below state
ment.

MySQL> Select Current_Role; 


First time you are going to get below output.
+--------------+
| Current_Role |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

If user TB or TB1 will try to select the data from TechBrothers database or run show databases statement, they will get permission denied error. They have to set the role first. Below statement can be used to set the role.

MySQL>  set Role Role_Select()

Now they should be able to use all the object on wich Role_Select has permission.
User can also set the default Role by using below statement so he/she does not have to set the role every time login. Root or DBA will be setting the default role for user.

MySQL> set default role Role_Select;

How to Drop Role in MySQL Server :

In MySQL server, you can use below statement to drop the role.

Syntax:
MySQL> Drop Role RoleName;

Example: 
Let's say if we have a role with name "Role_Select" and we want to drop, we can use below statement.

MySQL > Drop Role Role_Select;

20 comments:

  1. Thankyou for sharing this guide about how to create and drop role in mysql. i really need this.
    moreover take a look at this guide best vaccums for apartment

    ReplyDelete
  2. The macerating toilets are also known as the upflush toilets. They’ve made life easy for those who live in the basements and have no proper ways of sewage in their washrooms.

    ReplyDelete
  3. With a powerful 1,500-watt electric motor capable of spinning a steel auger bit at to 2,700 revolutions per minute (rpm), the XtremepowerUS Industrial Electric PostHole Digger is a great machine if you can to work close to a power source and don't want a gas-burning unit.

    ReplyDelete
  4. As for the connection to MySQL database, I found MySQL Components Delphi and solved such a problem.

    ReplyDelete
  5. Download Board Kings MOD APK latest Version to get unlimited gems/coins/rolls and enjoy the Board Kings hack apk to its fullest.

    ReplyDelete
  6. visit our website and download pandora mod apk. Pandora MOD APK (Unlocked Premium/Plus) is a useful application developed to cater to everyone's listening to music, Radio, or Podcasts.

    ReplyDelete
  7. Thankyou for sharing. visit our website beembuilds and check guide of best motherboard for video editing

    ReplyDelete
  8. Thanks for sharing. visit eagleapk and download agwhatsapp

    ReplyDelete
  9. visit our wesbite and read guides about tracking. you can also Enter tcs tracking number to track TCS shipments and get delivery status online. Contact TCS and get REST API docs.

    ReplyDelete
  10. thanks for your kind information. visit our website and check our latest reviews about best laptop for webcam

    ReplyDelete
  11. Thanks for sharing. visit our website and check laminate flooring redding ca

    ReplyDelete
  12. gutter guard installation can save homeowners a lot of time and energy attempting to unclog gutters. Once gutter guards are in place, they offer tremendous benefits to the home; however, the installation takes some work. If you want to install your gutter guards, you must have some knowledge about this process. Gutter guards come in several different types, and knowledge of these types and general best practices to install gutter guards is essential.

    ReplyDelete
  13. Squish time is playtime when you add Maui to your Squishmallows Squad! This ultra-squeezable, 20-inch, jumbo, yellow pineapple plush is made with high-quality and ultrasoft materials. Add this adorable pineapple plush to your Squishmallows Squad! Did you know that Maui is determined to conquer her fear of skateboarding and scuba diving? The soft pineapple plush is perfect to snuggle with while relaxing at home, watching a movie, or taking a long car or plane ride! These lovable and squeezable Squishmallows are the softest and cutest plush toys around! Join the Squad! axolotl squishmallow

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete