Tag Archives: Create new users and Grant Roles in MySQL

Create Roles in MySQL

Create Roles in MySQL

Table of Contents
___________________________________________________________________________________________________

1. Overview
2. How to Create Role?
3. How to Grant Privileges to Role?
4. Create new users and Grant Roles
5. How to find what roles granted to any users?
6. How to set Granted ROLE manually if not set?
7. How to Define Mandatory Roles?
8. How to Find assigned privileges from Role?
9. How to Revoke Privileges from Role?
10. Revoke Roles from User (OR) Role?
11. How to Drop Roles?
___________________________________________________________________________________________________


1. Overview

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them.


2. How to Create Roles?

mysql> CREATE ROLE 'DELL_APP_RO', 'DELL_APP_RW';
Query OK, 0 rows affected (0.00 sec)

mysql>



3. How to Grant Privileges to Role?

mysql> GRANT SELECT ON dell.* TO 'DELL_APP_RO';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON dell.* TO 'DELL_APP_RW';
Query OK, 0 rows affected (0.01 sec)

mysql>


4. Create new users and Grant Roles

-- Create new users
mysql> CREATE USER 'raj_ro_user'@'localhost' IDENTIFIED BY 'Raj_ro_user1%';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'raj_rw_user'@'localhost' IDENTIFIED BY 'Raj_rw_user1%';
Query OK, 0 rows affected (0.01 sec)

mysql>

-- Grants Roles 
mysql> GRANT DELL_APP_RO TO 'raj_ro_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT DELL_APP_RW TO 'raj_rw_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql>


5. How to find what roles granted to any users?

mysql> SELECT * FROM mysql.role_edges;
+-----------+-------------+-----------+-------------+-------------------+
| FROM_HOST | FROM_USER   | TO_HOST   | TO_USER     | WITH_ADMIN_OPTION |
+-----------+-------------+-----------+-------------+-------------------+
| %         | DELL_APP_RO | localhost | raj_ro_user | N                 |
| %         | DELL_APP_RW | localhost | raj_rw_user | N                 |
+-----------+-------------+-----------+-------------+-------------------+
2 rows in set (0.00 sec)

mysql>

------------ OR -------------

-- TO list ALL Roles  (you can notice roles also)

select * from mysql.user;


6. How to set Granted ROLE manually if not set?

[root@rac1 ~]# mysql -u raj_ro_user -h localhost -pRaj_ro_user1%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user ();
+-----------------------+
| current_user ()       |
+-----------------------+
| raj_ro_user@localhost |
+-----------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+------------------------------------------------------+
| Grants for raj_ro_user@localhost                     |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `raj_ro_user`@`localhost`      |
| GRANT `DELL_APP_RO`@`%` TO `raj_ro_user`@`localhost` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

mysql> set role DELL_APP_RO;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+-------------------+
| CURRENT_ROLE()    |
+-------------------+
| `DELL_APP_RO`@`%` |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+-------------------------------------------------------+
| Grants for raj_ro_user@localhost                      |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `raj_ro_user`@`localhost`       |
| GRANT SELECT ON `dell`.* TO `raj_ro_user`@`localhost` |
| GRANT `DELL_APP_RO`@`%` TO `raj_ro_user`@`localhost`  |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

############################################################

-- This command can set permanatly, no need to set manually

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql> set global activate_all_roles_on_login=1;
Query OK, 0 rows affected (0.00 sec)

mysql> 

#############################################################

-- Set multiple default roles for one user

SET DEFAULT ROLE ROLE1,ROLE2,ROLE3 TO TO 'username'@'localhost';

-- A default role can be assigned to multiple users in one command as above
SET DEFAULT ROLE ROLE1 TO 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost';


7. How To Define Mandatory Roles? 

To set and persist mandatory_roles at runtime,use a statement like this:
-- if you create new user, then automatically this role will be assigned.
-- these changes are fixed ever after restart MySQL.

mysql> CREATE ROLE MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT DELL_APP_RO,DELL_APP_RW TO MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.01 sec)

mysql>  GRANT SELECT ON sys.version to MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST mandatory_roles = 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
| MY_MANDATORY_ROLE |
+-------------------+
1 row in set (0.00 sec)

mysql> create user lenovo@localhost identified by 'Lenovo123%';
Query OK, 0 rows affected (0.01 sec)

mysql>

[root@rac1 ~]# mysql -u lenovo -h localhost -pLenovo123%
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+------------------+
| user()           |
+------------------+
| lenovo@localhost |
+------------------+
1 row in set (0.00 sec)

mysql> show grants;
+-------------------------------------------------------+
| Grants for lenovo@localhost                           |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `lenovo`@`localhost`            |
| GRANT `MY_MANDATORY_ROLE`@`%` TO `lenovo`@`localhost` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>


8. How to Find assigned privileges from Role?

mysql> show grants for DELL_APP_RO;
+-----------------------------------------------+
| Grants for DELL_APP_RO@%                      |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RO`@`%`       |
| GRANT SELECT ON `dell`.* TO `DELL_APP_RO`@`%` |
+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for DELL_APP_RW;
+-----------------------------------------------------------------------+
| Grants for DELL_APP_RW@%                                              |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RW`@`%`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `dell`.* TO `DELL_APP_RW`@`%` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>


9. How to Revoke Privileges from Role? and Revoke Roles from User?

mysql> REVOKE SELECT ON dell.* FROM DELL_APP_RO;
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE SELECT,INSERT,UPDATE,DELETE ON dell.* FROM DELL_APP_RW;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> show grants for DELL_APP_RO;
+-----------------------------------------+
| Grants for DELL_APP_RO@%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RO`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for DELL_APP_RW;
+-----------------------------------------+
| Grants for DELL_APP_RW@%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `DELL_APP_RW`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>


10. Revoke Roles from User (OR) Role?

mysql> SELECT * FROM mysql.role_edges;
+-----------+-------------+-----------+-------------------+-------------------+
| FROM_HOST | FROM_USER   | TO_HOST   | TO_USER           | WITH_ADMIN_OPTION |
+-----------+-------------+-----------+-------------------+-------------------+
| %         | DELL_APP_RO | %         | MY_MANDATORY_ROLE | N                 |
| %         | DELL_APP_RO | localhost | raj_ro_user       | N                 |
| %         | DELL_APP_RW | %         | MY_MANDATORY_ROLE | N                 |
| %         | DELL_APP_RW | localhost | raj_rw_user       | N                 |
+-----------+-------------+-----------+-------------------+-------------------+
4 rows in set (0.00 sec)

mysql>

-- Revoke Roles from user
mysql> REVOKE DELL_APP_RO FROM 'raj_ro_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE DELL_APP_RW FROM 'raj_rw_user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> 

-- Revoke Roles from Roles
mysql> REVOKE DELL_APP_RO FROM 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE DELL_APP_RW FROM 'MY_MANDATORY_ROLE';
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> SELECT * FROM mysql.role_edges;
Empty set (0.00 sec)

mysql>


11. How to Drop Roles?

mysql> select user from mysql.user;
+-------------------+
| user              |
+-------------------+
| DELL_APP_RO       |
| DELL_APP_RW       |
| MY_MANDATORY_ROLE |
| TEST_RO           |
| raj               |
| scott1            |
| srinu             |
| scott             |
| scott             |
| sugi              |
| scott             |
| teja              |
| bose              |
| lenovo            |
| lori              |
| may               |
| mike              |
| mysql.infoschema  |
| mysql.session     |
| mysql.sys         |
| raj               |
| raj_ro_user       |
| raj_rw_user       |
| root              |
| sunil             |
+-------------------+
25 rows in set (0.00 sec)

mysql>

mysql> DROP ROLE DELL_APP_RO;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP ROLE DELL_APP_RW;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DROP ROLE MY_MANDATORY_ROLE;
ERROR 3628 (HY000): The role `MY_MANDATORY_ROLE`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.
mysql>
mysql> select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
| MY_MANDATORY_ROLE |
+-------------------+
1 row in set (0.00 sec)

mysql>

mysql> SET PERSIST mandatory_roles = 'NONE';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@mandatory_roles;
+-------------------+
| @@mandatory_roles |
+-------------------+
| NONE              |
+-------------------+
1 row in set (0.00 sec)

mysql> DROP ROLE MY_MANDATORY_ROLE;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| TEST_RO          |
| raj              |
| scott1           |
| srinu            |
| scott            |
| scott            |
| sugi             |
| scott            |
| teja             |
| bose             |
| lenovo           |
| lori             |
| may              |
| mike             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| raj              |
| raj_ro_user      |
| raj_rw_user      |
| root             |
| sunil            |
+------------------+
22 rows in set (0.00 sec)

mysql>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Reference:
https://dev.mysql.com/doc/refman/8.0/en/roles.html