Manajemen Role dan Permission MySQL untuk Aplikasi Multi-User

Role di MySQL 8 memungkinkan Anda mengelompokkan hak akses dan memberikannya ke beberapa user sekaligus. Ketika permission perlu diubah, cukup ubah rolenya — semua user di dalamnya akan terpengaruh secara otomatis.

Panduan ini menggunakan studi kasus database bank dengan 4 peran: developer, manager, clerk, dan auditor.

Prasyarat

• Server dengan MySQL 8.x (dapat diinstall di Nusa Cloud VPS)
• Akses root ke MySQL (sudo mysql -u root -p)

1. Buat Database dan Tabel Sampel

CREATE DATABASE bank;
USE bank;

CREATE TABLE clients (
    client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    address VARCHAR(255)
) ENGINE = InnoDB;

CREATE TABLE clients_transactions (
    transaction_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    transaction_date DATE,
    transaction_type VARCHAR(1),
    amount DECIMAL(17, 2)
) ENGINE = InnoDB;

Tambahkan data sampel:

INSERT INTO clients (first_name, last_name, address) VALUES
('JOHN', 'DOE', '1ST STREET, FRESNO, CA XYZ'),
('MARY', 'SMITH', '2ND STREET, MIAMI, FL 123'),
('STEVE', 'MARK', '3RD STREET, PHOENIX, AZ ABC');

INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES
(1, '2026-01-21', 'C', 45.00),
(2, '2026-01-21', 'C', 60.00),
(3, '2026-01-21', 'C', 90.00),
(3, '2026-01-21', 'D', 14.00);

2. Buat Role

CREATE ROLE 'system_developer'@'localhost';
CREATE ROLE 'bank_manager'@'localhost';
CREATE ROLE 'bank_clerk'@'localhost';
CREATE ROLE 'bank_auditor'@'localhost';

Verifikasi:

SELECT User FROM mysql.user WHERE User LIKE 'bank_%' OR User LIKE 'system_%';

3. Beri Privilege ke Role

system_developer — akses penuh ke database bank:

GRANT ALL PRIVILEGES ON bank.* TO 'system_developer'@'localhost';

bank_manager — CRUD di tabel transaksi, hanya INSERT/SELECT/UPDATE di tabel clients:

GRANT INSERT, SELECT, UPDATE ON bank.clients TO 'bank_manager'@'localhost';
GRANT INSERT, SELECT, UPDATE, DELETE ON bank.clients_transactions TO 'bank_manager'@'localhost';

bank_clerk — hanya INSERT dan SELECT:

GRANT INSERT, SELECT ON bank.clients TO 'bank_clerk'@'localhost';
GRANT INSERT, SELECT ON bank.clients_transactions TO 'bank_clerk'@'localhost';

bank_auditor — read-only:

GRANT SELECT ON bank.clients TO 'bank_auditor'@'localhost';
GRANT SELECT ON bank.clients_transactions TO 'bank_auditor'@'localhost';

Cek privilege role:

SHOW GRANTS FOR 'bank_auditor'@'localhost';

4. Buat User

CREATE USER 'mary'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_MARY';
CREATE USER 'raphael'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_RAPHAEL';
CREATE USER 'james'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_JAMES';
CREATE USER 'andrew'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_ANDREW';
CREATE USER 'bob'@'localhost' IDENTIFIED WITH mysql_native_password BY 'PASSWORD_BOB';

⚠️ Warning: Ganti PASSWORD_* dengan password kuat. Jangan gunakan password yang sama antar user.

5. Assign User ke Role

GRANT 'system_developer'@'localhost' TO 'mary'@'localhost';
GRANT 'bank_manager'@'localhost' TO 'raphael'@'localhost';
GRANT 'bank_clerk'@'localhost' TO 'james'@'localhost';
GRANT 'bank_clerk'@'localhost' TO 'andrew'@'localhost';
GRANT 'bank_auditor'@'localhost' TO 'bob'@'localhost';

Terapkan default role:

SET DEFAULT ROLE ALL TO
    'mary'@'localhost',
    'raphael'@'localhost',
    'james'@'localhost',
    'andrew'@'localhost',
    'bob'@'localhost';

6. Verifikasi

Login sebagai auditor:

mysql -u bob -p

Cek role aktif:

SELECT current_role();

Output:

+----------------------------+
| current_role()             |
+----------------------------+
| `bank_auditor`@`localhost` |
+----------------------------+

Coba query data:

USE bank;
SELECT * FROM clients;

Coba INSERT (harus ditolak):

INSERT INTO clients (first_name, last_name, address) VALUES ('TEST', 'USER', 'ADDR');

Seharusnya error:

ERROR 1142 (42000): INSERT command denied to user 'bob'@'localhost'

Keuntungan Role-Based Permission

Tanpa Role	Dengan Role
GRANT per user (1000 clerk = 1000 GRANT)	GRANT per role (1 role for 1000 clerk)
Update permission satu per satu	Update 1 role, semua user terpengaruh
Rentan inkonsistensi	Konsisten terpusat

Kesimpulan

Role MySQL memudahkan manajemen permission pada aplikasi multi-user. Pendekatan ini sangat direkomendasikan untuk aplikasi production yang memerlukan kontrol akses granular di Nusa Cloud VPS.

Butuh Bantuan?

Tiket Bantuan