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

In this guide, we will walk you through the steps of setting up MySQL roles on a database server hosted on Alibaba Cloud ECS instance running Ubuntu 16.

By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

A role is a collection of permissions that are assigned to users on a database. Roles simplify the assignment of privileges in a database by restricting what a specific group of users can do.

The basic idea behind creating roles is to grant users least privileges. This means a user is only given access to database properties that they require and the rest of the permissions are blocked. With such limited access, it will be impossible for such users to harm the database.

Roles are based on different access levels that can create, modify, update or even drop database objects. Usually, roles are created after carefully analyzing the needs of each user. Since MySQL 8.0 supports role-management capabilities, the entire process is very easy and straightforward.

In this guide, we will walk you through the steps of setting up MySQL roles on a database server hosted on Alibaba Cloud Elastic Compute Service (ECS) instance running Ubuntu 16.04.

Prerequisites

To follow along with this guide, make sure you have the following:

A valid Alibaba Cloud account. If you are new to Alibaba Cloud, you can sign up now and get up to $1200 worth in free trial credit to test more than 40 cloud products.
An ECS instance running Ubuntu 16.04.
A non-root user that can perform sudo privileges on your Ubuntu server

Step 1: Installing MySQL Community Server Version 8.0

The default Ubuntu repository does not include the newest MySQL version by default. So, to install MySQL 8.0, we will need to add its package information on the repository.

To do this, first cd to the tmp directory:

$ cd /tmp

Then, use Linux wget command to pull the MySQL 8.0 deb file from MySQL download page:

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.11-1_all.deb

Next, run the command below and choose OK on the configuration prompt options that appear. It should be the last option on the list

$ sudo dpkg -i mysql-apt-config_0.8.11-1_all.deb

Before you install MySQL 8.0, run apt-get update to refresh the package information index.

$ sudo apt-get update

Then, install MySQL server:

$ sudo apt-get install mysql-server mysql-client

Towards the end of the installation, you will be prompted to provide the root password of your MySQL server. The installation should then complete without any problems.

Step 2: Creating MySQL Roles

Before we start creating roles, login to the MySQL 8.0 community server:

$ sudo mysql -uroot -p

Enter the root password when prompted and hit Enter to continue.

The basic syntax for creating roles is shown below. A role consists of the name part and host part.

mysql>CREATE ROLE '[Role Name]'@'[Hostname]';

Example:

mysql>CREATE ROLE 'bank_manager'@'localhost';

If the hostname part is omitted during role creation, MySQL defaults to '%'.

You can also create multiple roles in one MySQL command by separating them with commas as shown below:

mysql>CREATE ROLE [Role Name 1], [Role Name 2], [Role Name 3] ;

Example:

mysql>CREATE ROLE cashier, secretary, supervisor ;

Step 3: Assigning Privileges to MySQL Role

To assign privileges to a role, use the GRANT command as shown below:

mysql> GRANT [Privileges] ON [Database Property] TO '[Role Name]';

To use the above example, let us first create a sample database and name it "xyz_bank".

mysql> Create database xyz_bank;

We can now go ahead and grant privileges to the role that we created above to our database.

mysql>GRANT ALL ON xyz_bank.* TO 'bank_manager'@'localhost';

Alternatively, if you wish to assign just a few privileges to a role, you may separate the different permissions with commas.

Example:

mysql>GRANT Create, Select, Update, Delete ON xyz_bank.* TO 'bank_manager'@'localhost';

You may confirm the privileges associated with any role by running the command below:

mysql> SHOW GRANTS FOR '[Role Name]'@'[Hostname]';

Example:

mysql> SHOW GRANTS FOR 'bank_manager'@'localhost';

Output:

+--------------------------------------------------------------------+
| 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

Once you have created a role and assigned the right permissions, you can assign the role to a user using the syntax shown below:

mysql> GRANT '[Role Name]'@'[Host Name]' TO '[Username]'@'[Host Name]';

Before we use the above example, let's first create a user named "james".

mysql> CREATE USER 'james'@'localhost' IDENTIFIED BY 'jamespass';

We can now link "james" to our previously created role "bank_manager".

mysql> GRANT 'bank_manager'@'localhost' TO 'james'@'localhost';

You can associate multiple users to a specific role depending on your application needs by repeating the command above.

Step 5: Revoking Roles and Roles Privileges

In MySQL 8.0, you can remove a user from a role or just revoke the privileges associated with a role. This will depend on your needs and the permissions that you want to be associated with a user or a role at any given time.

Removing a User from a MySQL Role

If you no longer want a user to be linked to a certain role, you can revoke the privilege using the command below:

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

Example:

mysql> REVOKE 'bank_manager'@'localhost' FROM 'james'@'localhost';

Revoking Privileges from MySQL Roles

You can also revoke the privileges linked to a particular role. See the syntax below:

mysql> REVOKE [Privileges] ON [Database Property] FROM '[Role Name]'@'[Host Name]';

Example:

mysql> REVOKE All ON xyz_bank.* FROM 'bank_manager'@'localhost';

It is always good to double check if the changes were reflected on the database by running the show grants command:

mysql> SHOW GRANTS FOR 'bank_manager'@'localhost';

Output:

+--------------------------------------------------+
| Grants for bank_manager@localhost                |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `bank_manager`@`localhost` |
+--------------------------------------------------+

Step 6: Removing Roles from MySQL Server

To completely remove the role from the MySQL server, use the drop statement as shown below:

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

Example:

mysql> DROP ROLE 'bank_manager'@'localhost';

Step 7: Configuring Default and Mandatory Roles

You can specify a specific role that you want to be granted explicitly to all accounts on the MySQL server configuration file. For instance, you can assign users to a default role with privileges for viewing a certain table's data.

To do this, open the MySQL configuration file using nano text editor:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Then specify the mandatory roles by separating them with commas using the syntax below:

[mysqld] 
...
mandatory_roles='role1, role2, role3, role...n'

Example:

[mysqld] 
...
mandatory_roles='bank_manager@localhost'

When you are done editing the file, press CTRL+X, Y and hit Enter to save the file.

Then restart MySQL server for the changes to take effect:

$ sudo service mysql restart

Step 8: Activating Roles on MySQL Server

Assigning roles to a user or including them on the mandatory_roles variable on the configuration file does not explicitly activate them at run time.

In order for the roles to be activated each time a user authenticates to the system, use the SET_DEFAULT_ROLE command to activate the roles assigned to the user.

mysql> SET DEFAULT ROLE ALL TO user1, user2, user3, user..n;

Also, you might find it easier to force all granted and mandatory roles to be activated immediately by modifying the activate_all_roles_on_login variable on the mysql configuration file:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] 
...
mandatory_roles='bank_manager@localhost'
activate_all_roles_on_login=on

Then, save the configuration file by pressing CTRL+X, Y and Enter. Remember to restart MySQL server for the changes to take effect:

$ sudo service mysql restart

Conclusion

In this guide, we have taken you through the steps of installing MySQL server 8.0 and managing roles on your Ubuntu 16.04 server hosted on Alibaba Cloud. While we won't promise that this is an exhaustive list of all commands used in MySQL role management, it is a good start for database administrators.

We would advise you to implement roles when assigning privileges to a large number of users to simplify the process. If you are new to Alibaba Cloud, sign up now and get up to $1200 worth in free credit to test MySQL server role management and more than 40 cloud products.

Community

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

Prerequisites

Step 1: Installing MySQL Community Server Version 8.0

Step 2: Creating MySQL Roles

Step 3: Assigning Privileges to MySQL Role

Step 4: Assigning Users to Roles

Step 5: Revoking Roles and Roles Privileges

Removing a User from a MySQL Role

Revoking Privileges from MySQL Roles

Step 6: Removing Roles from MySQL Server

Step 7: Configuring Default and Mandatory Roles

Step 8: Activating Roles on MySQL Server

Conclusion

Read previous post:

Read next post:

francisndungu

You may also like

Comments

francisndungu

Related Products

ApsaraDB RDS for MySQL

ApsaraDB RDS for PostgreSQL

ApsaraDB RDS for MariaDB

ApsaraDB RDS for SQL Server