Role-Based Access Control in Databases

Introduction

Role-Based Access Control (RBAC) is a security model used to manage permissions in databases. Instead of assigning permissions directly to users, RBAC groups users into roles, and each role is granted specific permissions. This approach simplifies access management, enhances security, and ensures users only have access to the data and operations they need.

In this tutorial, we will cover:

• The importance of RBAC in databases
• Implementing RBAC in MySQL, PostgreSQL, and SQL Server
• Best practices for managing roles and permissions

Why Use Role-Based Access Control?

RBAC is widely used in organizations to:

• Enhance security by restricting access to sensitive data.
• Simplify user management by grouping users into predefined roles.
• Ensure compliance with data privacy regulations such as GDPR and HIPAA.
• Minimize human error by preventing unauthorized modifications.

Implementing RBAC in Different Databases

1. Role-Based Access Control in MySQL

MySQL provides user roles that simplify permission management.

Step 1: Create Roles

CREATE ROLE read_only;
CREATE ROLE editor;
CREATE ROLE admin;

Step 2: Assign Permissions to Roles

GRANT SELECT ON database_name.* TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO editor;
GRANT ALL PRIVILEGES ON database_name.* TO admin;

Step 3: Assign Roles to Users

GRANT read_only TO user1;
GRANT editor TO user2;
GRANT admin TO user3;

Step 4: Activate Role for a User

SET ROLE read_only;

Best Practice: Use roles instead of assigning direct privileges to users.

2. Role-Based Access Control in PostgreSQL

PostgreSQL has a robust role-based access system where roles can be both users and groups.

Step 1: Create Roles

CREATE ROLE read_only;
CREATE ROLE editor;
CREATE ROLE admin;

Step 2: Assign Permissions to Roles

GRANT CONNECT ON DATABASE mydb TO read_only, editor, admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO editor;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

Step 3: Create Users and Assign Roles

CREATE USER user1 WITH PASSWORD 'password123';
GRANT read_only TO user1;

CREATE USER user2 WITH PASSWORD 'password456';
GRANT editor TO user2;

Step 4: Enable Default Role for Users

ALTER USER user1 SET ROLE read_only;

Best Practice: Use pg_roles to manage and audit role permissions.

3. Role-Based Access Control in SQL Server

SQL Server implements RBAC using database roles.

Step 1: Create Roles

CREATE ROLE read_only;
CREATE ROLE editor;
CREATE ROLE admin;

Step 2: Assign Permissions to Roles

GRANT SELECT ON SCHEMA::dbo TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO editor;
GRANT CONTROL ON SCHEMA::dbo TO admin;

Step 3: Create Users and Assign Roles

CREATE LOGIN user1 WITH PASSWORD = 'password123';
CREATE USER user1 FOR LOGIN user1;
ALTER ROLE read_only ADD MEMBER user1;

CREATE LOGIN user2 WITH PASSWORD = 'password456';
CREATE USER user2 FOR LOGIN user2;
ALTER ROLE editor ADD MEMBER user2;

Best Practice: Use sys.database_principals to track user roles and permissions.

Managing and Auditing Role-Based Access

1. Reviewing Assigned Roles

To list all roles and users assigned to them:

MySQL

SELECT * FROM mysql.db WHERE user='user1';

PostgreSQL

SELECT grantee, privilege_type FROM information_schema.role_table_grants;

SQL Server

SELECT dp.name AS RoleName, m.name AS UserName
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id;

2. Removing User Access

To revoke a role from a user:

MySQL

REVOKE read_only FROM user1;

PostgreSQL

REVOKE read_only FROM user1;

SQL Server

ALTER ROLE read_only DROP MEMBER user1;

Best Practices for Role-Based Access Control

• Follow the principle of least privilege – Users should only have access to the data they need.
• Regularly audit user roles and permissions – Remove inactive users and update permissions as needed.
• Use predefined roles – Avoid assigning direct privileges to users.
• Implement multi-layer security – Combine RBAC with row-level security, encryption, and monitoring.
• Apply database security policies – Ensure compliance with industry regulations.

Conclusion

Role-Based Access Control (RBAC) is a powerful mechanism for managing database security. It simplifies user access management, reduces security risks, and ensures only authorized users can access sensitive data.

By implementing RBAC correctly in MySQL, PostgreSQL, and SQL Server, organizations can enhance security while maintaining flexibility in access control.