MySql - Managing roles and access control

Managing Roles and Access Control in MySQL

Managing roles and access control is an essential part of database administration. MySQL provides powerful tools for implementing fine-grained access control to ensure users have only the privileges they need to perform their tasks. With the introduction of roles in MySQL 8.0, managing access became more flexible and scalable. This document provides an in-depth explanation of how to manage roles and access control in MySQL, covering user creation, roles, privilege assignment, and security best practices.

Understanding Access Control in MySQL

Access control in MySQL is implemented through the following components:

• Users: Individual accounts that connect to the MySQL server.
• Privileges: Permissions granted to users or roles to perform actions (e.g., SELECT, INSERT, UPDATE).
• Roles: Named collections of privileges that can be assigned to users or other roles.

Why Use Roles?

Managing individual user privileges becomes complex as the number of users and permissions grows. Roles simplify this by allowing privileges to be grouped logically. You can then assign a role to multiple users, ensuring consistency and easier management.

Creating Users

Before assigning roles, users must be created. Use the CREATE USER statement:

CREATE USER 'developer'@'localhost' IDENTIFIED BY 'devpass123';

CREATE USER 'manager'@'localhost' IDENTIFIED BY 'managerpass';

Creating Roles

Roles are created similarly to users, but with the CREATE ROLE statement:

CREATE ROLE 'read_only';
CREATE ROLE 'read_write';
CREATE ROLE 'admin';

These roles do not allow login and are meant for grouping privileges.

Granting Privileges to Roles

After creating roles, assign privileges to them using the GRANT command.

Grant Read-Only Access

GRANT SELECT ON mydb.* TO 'read_only';

Grant Read-Write Access

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write';

Grant Administrative Access

GRANT ALL PRIVILEGES ON *.* TO 'admin' WITH GRANT OPTION;

Assigning Roles to Users

Assign the defined roles to users with the following syntax:

GRANT 'read_only' TO 'developer'@'localhost';
GRANT 'read_write' TO 'manager'@'localhost';

A user can have multiple roles assigned.

Activating and Using Roles

After granting roles, you must activate them for the session or set them as the user’s default.

Set Default Role

SET DEFAULT ROLE 'read_only' TO 'developer'@'localhost';

Activate Role for Session

SET ROLE 'read_only';

To activate all roles granted to the user:

SET ROLE ALL;

Viewing Assigned Roles

Check what roles are granted to a user:

SELECT * FROM information_schema.enabled_roles;

Or use the SHOW GRANTS command:

SHOW GRANTS FOR 'developer'@'localhost';

Revoking Roles and Privileges

Use the REVOKE statement to remove roles or privileges.

Revoke Role from User

REVOKE 'read_only' FROM 'developer'@'localhost';

Revoke Privileges from Role

REVOKE SELECT ON mydb.* FROM 'read_only';

Dropping Roles

To delete a role:

DROP ROLE 'read_only';

This removes the role but not the users. You must also revoke the role from all users before dropping it.

Role Inheritance

MySQL supports role nesting, where one role can be granted to another. This creates a hierarchy of privileges.

GRANT 'read_only' TO 'read_write';

Now, any user with read_write also has the privileges of read_only.

Example: Defining an Access Control Structure

Step 1: Create Roles

CREATE ROLE 'analyst';
CREATE ROLE 'engineer';
CREATE ROLE 'project_lead';

Step 2: Grant Privileges to Roles

GRANT SELECT ON company_db.* TO 'analyst';
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'engineer';
GRANT ALL PRIVILEGES ON company_db.* TO 'project_lead';

Step 3: Create Users

CREATE USER 'jane'@'localhost' IDENTIFIED BY 'janepass';
CREATE USER 'tom'@'localhost' IDENTIFIED BY 'tompass';
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'alicepass';

Step 4: Assign Roles

GRANT 'analyst' TO 'jane'@'localhost';
GRANT 'engineer' TO 'tom'@'localhost';
GRANT 'project_lead' TO 'alice'@'localhost';

Step 5: Set Default Roles

SET DEFAULT ROLE 'analyst' TO 'jane'@'localhost';
SET DEFAULT ROLE 'engineer' TO 'tom'@'localhost';
SET DEFAULT ROLE 'project_lead' TO 'alice'@'localhost';

Best Practices for Role-Based Access Control

• Use roles to group related privileges for easier management.
• Follow the principle of least privilege—assign only the access required for each role.
• Review role assignments periodically for outdated or excessive permissions.
• Avoid using wildcard host (%) unless absolutely necessary.
• Use naming conventions for roles (e.g., app_readonly, app_admin).

Auditing and Monitoring Roles

To keep track of who has which roles and privileges, use the following queries:

List All Roles

SELECT DISTINCT USER FROM mysql.user WHERE IS_ROLE = 'Y';

List Users with Their Roles

SELECT grantee, role_name 
FROM information_schema.applicable_roles 
ORDER BY grantee;

List Privileges for a Role

SHOW GRANTS FOR 'read_only';

Enforcing Access Control

In addition to granting or revoking privileges, you can further enforce access with security plugins:

• validate_password: Enforce strong password policies.
• audit_log: Monitor login attempts and SQL operations (available in MySQL Enterprise).
• SSL/TLS: Enforce secure connections using encrypted channels.

Requiring SSL for a Role

ALTER USER 'analyst'@'localhost' REQUIRE SSL;

Backup and Restore of Roles

Roles and privileges are stored in the mysql system database. Backup using:

mysqldump -u root -p mysql user db tables_priv role_edges > user_roles_backup.sql

To restore:

mysql -u root -p mysql < user_roles_backup.sql

Managing Roles with MySQL Workbench

MySQL Workbench offers a GUI to manage users and roles:

• Open Workbench and connect to the server.
• Navigate to “Users and Privileges”.
• Create and manage roles and privileges through intuitive tabs.

This is particularly useful for DBAs who prefer graphical interfaces.

Differences Between Users and Roles

MySQL's role-based access control (RBAC) system provides a scalable and secure way to manage permissions. By creating roles and assigning them to users, DBAs can simplify privilege management, enforce least privilege, and maintain consistent access policies across environments. MySQL 8.0 enhances this process with nested roles, password policies, and advanced monitoring tools. Following best practices and leveraging tools like MySQL Workbench or automation scripts ensures that your access control system remains robust, secure, and auditable.