How to Implement MySQL Roles on Alibaba Cloud ECS Running Ubuntu 16.04

Prerequisites

Step 1: Installing MySQL Community Server Version 8.0

$ cd /tmp
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.11-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.11-1_all.deb
$ sudo apt-get update
$ sudo apt-get install mysql-server mysql-client

Step 2: Creating MySQL Roles

$ sudo mysql -uroot -p
mysql>CREATE ROLE '[Role Name]'@'[Hostname]';
mysql>CREATE ROLE 'bank_manager'@'localhost';
mysql>CREATE ROLE [Role Name 1], [Role Name 2], [Role Name 3] ;
mysql>CREATE ROLE cashier, secretary, supervisor ;

Step 3: Assigning Privileges to MySQL Role

mysql> GRANT [Privileges] ON [Database Property] TO '[Role Name]';
mysql> Create database xyz_bank;
mysql>GRANT ALL ON xyz_bank.* TO 'bank_manager'@'localhost';
mysql>GRANT Create, Select, Update, Delete ON xyz_bank.* TO 'bank_manager'@'localhost';
mysql> SHOW GRANTS FOR '[Role Name]'@'[Hostname]';
mysql> SHOW GRANTS FOR 'bank_manager'@'localhost';
+--------------------------------------------------------------------+
| Grants for bank_manager@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `bank_manager`@`localhost` |
| GRANT ALL PRIVILEGES ON `xyz_bank`.* TO `bank_manager`@`localhost` |
+--------------------------------------------------------------------+

Step 4: Assigning Users to Roles

mysql> GRANT '[Role Name]'@'[Host Name]' TO '[Username]'@'[Host Name]';
mysql> CREATE USER 'james'@'localhost' IDENTIFIED BY 'jamespass';
mysql> GRANT 'bank_manager'@'localhost' TO 'james'@'localhost';

Step 5: Revoking Roles and Roles Privileges

Removing a User from a MySQL Role

mysql> REVOKE '[Role Name]'@'[Hostname]' FROM '[User Name]'@'[Hostname]';
mysql> REVOKE 'bank_manager'@'localhost' FROM 'james'@'localhost';

Revoking Privileges from MySQL Roles

mysql> REVOKE [Privileges] ON [Database Property] FROM '[Role Name]'@'[Host Name]';
mysql> REVOKE All ON xyz_bank.* FROM 'bank_manager'@'localhost';
mysql> SHOW GRANTS FOR 'bank_manager'@'localhost';
+--------------------------------------------------+
| Grants for bank_manager@localhost |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `bank_manager`@`localhost` |
+--------------------------------------------------+

Step 6: Removing Roles from MySQL Server

mysql> DROP ROLE '[Role Name]'@'[Host Name]';
mysql> DROP ROLE 'bank_manager'@'localhost';

Step 7: Configuring Default and Mandatory Roles

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld] 
...
mandatory_roles='role1, role2, role3, role...n'
[mysqld] 
...
mandatory_roles='bank_manager@localhost'
$ sudo service mysql restart

Step 8: Activating Roles on MySQL Server

mysql> SET DEFAULT ROLE ALL TO user1, user2, user3, user..n;
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf[mysqld] 
...
mandatory_roles='bank_manager@localhost'
activate_all_roles_on_login=on
$ sudo service mysql restart

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store