Menu
📱 Lihat versi lengkap (non-AMP)
MySQL Security Database Administration

Panduan Lengkap User Management dan Privileges di MySQL: Best Practices Keamanan

Editor: Hendra WIjaya
Update: 3 February 2026
Baca: 5 menit

Panduan Lengkap User Management dan Privileges di MySQL: Best Practices Keamanan

Keamanan database dimulai dari user management yang tepat. Setelah mengaudit ratusan sistem production, saya menyaksikan banyak security breach yang disebabkan oleh user privileges yang terlalu luas. Artikel ini akan membahas prinsip least privilege dan implementasinya di MySQL.

Prinsip Least Privilege

Definisi

  • User hanya mendapatkan privileges yang diperlukan untuk tugasnya
  • Tidak memberikan GRANT ALL secara default
  • Regular audit privileges
  • Revoke privileges yang tidak digunakan

Konsekuensi Privileges Terlalu Luas

  • Data breach jika credential leaked
  • Accidental deletion/modifikasi
  • Compliance violations (GDPR, PCI-DSS)
  • Difficult audit trail

User Creation Best Practices

1. Strong Password Requirements

MySQL 8.0 dengan validate_password:

-- Install plugin (jika belum)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Konfigurasi password policy
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

Verifikasi policy:

SHOW VARIABLES LIKE 'validate_password%';

2. Create User dengan Host Spesifik

-- JANGAN gunakan '%' untuk production
-- Create user dengan IP atau hostname spesifik

-- Untuk localhost only
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Str0ngP@ssw0rd!';

-- Untuk subnet spesifik
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'Str0ngP@ssw0rd!';

-- Untuk hostname spesifik (dengan DNS atau /etc/hosts)
CREATE USER 'app_user'@'appserver01.internal' IDENTIFIED BY 'Str0ngP@ssw0rd!';

3. Expired Password Policy

-- Set password expiration
ALTER USER 'app_user'@'localhost' 
PASSWORD EXPIRE INTERVAL 90 DAY;

-- Require current password untuk ganti password
ALTER USER 'app_user'@'localhost' 
PASSWORD HISTORY 5;  -- Tidak boleh pakai 5 password terakhir

Grant Privileges: Least Privilege Implementation

1. Application User (Read-Write)

-- Create user untuk aplikasi
CREATE USER 'ecommerce_app'@'10.0.0.%' 
IDENTIFIED BY 'Ec0mApp#2024!Sec';

-- Grant privileges minimal
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* 
TO 'ecommerce_app'@'10.0.0.%';

-- Grant untuk specific procedures (lebih aman)
GRANT EXECUTE ON PROCEDURE ecommerce.process_order 
TO 'ecommerce_app'@'10.0.0.%';

-- Revoke DROP privileges (hati-hati!)
-- REVOKE DROP ON ecommerce.* FROM 'ecommerce_app'@'10.0.0.%';

2. Read-Only User (Reporting)

-- User untuk reporting/dashboard
CREATE USER 'report_user'@'10.0.1.%' 
IDENTIFIED BY 'R3p0rt#R34d0nly!';

-- Hanya SELECT
GRANT SELECT ON ecommerce.* 
TO 'report_user'@'10.0.1.%';

-- Tidak bisa: INSERT, UPDATE, DELETE, CREATE, DROP, ALTER

3. Backup User

-- User khusus backup
CREATE USER 'backup_user'@'localhost' 
IDENTIFIED BY 'B4ckup#S3cur3!';

-- Privileges minimal untuk backup
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT 
ON *.* 
TO 'backup_user'@'localhost';

-- Tidak perlu: INSERT, UPDATE, DELETE, CREATE, DROP

4. Admin User (Limited)

-- Admin yang tidak super
CREATE USER 'db_admin'@'10.0.0.%' 
IDENTIFIED BY 'Adm1n#DB#2024!';

-- Privileges admin
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
      ALTER, INDEX, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, 
      ALTER ROUTINE, EXECUTE, EVENT, TRIGGER 
ON ecommerce.* 
TO 'db_admin'@'10.0.0.%';

-- WITH GRANT OPTION (berhati-hati!)
-- GRANT GRANT OPTION ON ecommerce.* TO 'db_admin'@'10.0.0.%';

Role-Based Access Control (RBAC)

1. Create Roles

-- Create roles untuk berbagai fungsi
CREATE ROLE 'app_readwrite';
CREATE ROLE 'app_readonly';
CREATE ROLE 'backup_operator';
CREATE ROLE 'db_developer';

2. Grant Privileges ke Roles

-- App read-write role
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.* 
TO 'app_readwrite';

-- App read-only role
GRANT SELECT ON ecommerce.* 
TO 'app_readonly';

-- Backup role
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD 
ON *.* 
TO 'backup_operator';

-- Developer role
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, 
      CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE 
ON ecommerce.* 
TO 'db_developer';

3. Assign Roles ke Users

-- Assign role ke user
GRANT 'app_readwrite' TO 'ecommerce_app'@'10.0.0.%';
GRANT 'app_readonly' TO 'report_user'@'10.0.1.%';
GRANT 'backup_operator' TO 'backup_user'@'localhost';
GRANT 'db_developer' TO 'developer01'@'10.0.2.%';

-- Set default role (auto-activate saat login)
SET DEFAULT ROLE 'app_readwrite' TO 'ecommerce_app'@'10.0.0.%';
SET DEFAULT ROLE 'app_readonly' TO 'report_user'@'10.0.1.%';

4. Activate dan Deactivate Roles

-- User mengaktifkan role saat session
SET ROLE 'app_readwrite';

-- Aktifkan semua roles yang dimiliki
SET ROLE ALL;

-- Nonaktifkan role
SET ROLE NONE;

-- Cek role yang aktif
SELECT CURRENT_ROLE();

Audit dan Monitoring

1. Audit User Creation dan Changes

-- Enable general log (temporary)
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';  -- atau 'FILE'

-- Query untuk audit
SELECT 
    event_time,
    user_host,
    argument
FROM mysql.general_log
WHERE argument LIKE '%CREATE USER%'
   OR argument LIKE '%GRANT%'
   OR argument LIKE '%ALTER USER%'
ORDER BY event_time DESC
LIMIT 50;

2. Cek Current Privileges

-- Show grants untuk user spesifik
SHOW GRANTS FOR 'ecommerce_app'@'10.0.0.%';

-- Show semua user
SELECT User, Host FROM mysql.user;

-- Show privileges untuk semua user
SELECT 
    GRANTEE,
    TABLE_SCHEMA,
    PRIVILEGE_TYPE,
    IS_GRANTABLE
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = 'ecommerce'
ORDER BY GRANTEE, PRIVILEGE_TYPE;

3. Audit Active Connections

-- Cek siapa yang terhubung
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as query_preview
FROM information_schema.PROCESSLIST
WHERE USER != 'system user'
ORDER BY TIME DESC;

4. Login Attempts Monitoring

-- Cek failed login attempts
SHOW GLOBAL STATUS LIKE 'Connection_errors%';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Aborted_clients';

Security Hardening

1. Remove Anonymous Users

-- Cek anonymous users
SELECT User, Host FROM mysql.user WHERE User = '';

-- Delete anonymous users
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

2. Remove Test Database

-- Remove test database dan privileges
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db = 'test' OR Db = 'test\\_%';
FLUSH PRIVILEGES;

3. Secure Root Access

-- Ganti nama root (optional)
RENAME USER 'root'@'localhost' TO 'admin'@'localhost';

-- Atau buat admin baru dan disable root
CREATE USER 'sysadmin'@'localhost' IDENTIFIED BY 'S3cur3#R00t!';
GRANT ALL ON *.* TO 'sysadmin'@'localhost' WITH GRANT OPTION;

-- Update root (jangan delete, disable saja)
ALTER USER 'root'@'localhost' ACCOUNT LOCK;

4. Disable Remote Root

-- Pastikan root hanya bisa localhost
DELETE FROM mysql.user 
WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
FLUSH PRIVILEGES;

5. SSL/TLS Requirements

-- Require SSL untuk user tertentu
ALTER USER 'ecommerce_app'@'10.0.0.%' 
REQUIRE SSL;

-- Require X509 (certificate-based)
ALTER USER 'admin'@'localhost' 
REQUIRE X509;

Privilege Escalation Prevention

1. Limit SUPER Privilege

-- Hanya berikan SUPER untuk maintenance window
CREATE USER 'maintenance'@'localhost' 
IDENTIFIED BY 'M4int#SUPER!';
GRANT SUPER ON *.* TO 'maintenance'@'localhost';

-- Lock setelah maintenance
ALTER USER 'maintenance'@'localhost' ACCOUNT LOCK;

2. GRANT OPTION Control

-- Berhati-hati dengan WITH GRANT OPTION
-- Hanya untuk senior admin
GRANT ALL ON ecommerce.* TO 'senior_admin'@'10.0.0.%' 
WITH GRANT OPTION;

-- User tanpa GRANT OPTION tidak bisa grant privileges ke user lain

3. Monitor Privilege Changes

-- Query untuk detect privilege escalation
SELECT 
    user,
    host,
    grant_priv,
    super_priv,
    create_user_priv
FROM mysql.user
WHERE grant_priv = 'Y' 
   OR super_priv = 'Y' 
   OR create_user_priv = 'Y';

Regular Maintenance

1. Quarterly Privilege Review

-- Script untuk review
SELECT 
    GRANTEE,
    COUNT(*) as privilege_count,
    GROUP_CONCAT(PRIVILEGE_TYPE) as privileges
FROM information_schema.SCHEMA_PRIVILEGES
GROUP BY GRANTEE
ORDER BY privilege_count DESC;

-- Identifikasi user dengan privileges terlalu banyak

2. Revoke Unused Privileges

-- Revoke privileges yang tidak perlu
REVOKE INSERT, DELETE ON ecommerce.* 
FROM 'report_user'@'10.0.1.%';

-- Drop user yang tidak aktif
-- Cek last login terlebih dahulu

3. Password Rotation

-- Rotate password setiap 90 hari
ALTER USER 'ecommerce_app'@'10.0.0.%' 
IDENTIFIED BY 'N3wStr0ngP@ssw0rd!2024';

Kesimpulan

User management yang baik adalah defense layer pertama dalam keamanan database:

  1. Principle of Least Privilege: User hanya dapat apa yang diperlukan
  2. Role-Based Access: Organisir privileges dengan roles
  3. Strong Authentication: Password policy dan encryption
  4. Regular Audit: Review dan monitor privileges
  5. Quick Revocation: Siap revoke jika ada masalah

Dengan praktik ini, Anda secara signifikan mengurangi attack surface database Anda.

Artikel Terkait

Bagikan:

Link Postingan: https://www.tirinfo.com/panduan-user-management-privileges-mysql/