Users, Roles, Privileges, and Security

Concept

MySQL security has several layers:

Accounts are 'user'@'host' pairs; the host part can be a name, IP, subnet wildcard ('10.0.0.%'), or '%' (any host).

Authentication plugins: caching_sha2_password (default since 8.0), mysql_native_password (legacy; disabled by default in 8.4 and removed in 9.0), auth_socket (Unix socket OS-user authentication), LDAP/Kerberos/PAM (Enterprise or plugins).

Privileges are granted at global (*.*), database (db.*), table, column, and routine level.

Roles (8.0+) are named collections of privileges.

flowchart LR
    N[Network<br/>bind-address, firewall, TLS] --> A[Authentication<br/>'user'@'host' + plugin]
    A --> P[Authorization<br/>privileges and roles]
    P --> D[Data protection<br/>views, encryption at rest]
    D --> L[Auditing and monitoring]

Why It Matters

Most database breaches come from weak passwords, over-privileged application accounts, and databases reachable from the internet. MySQL gives fine-grained control — if you use it.

Syntax

CREATE USER 'name'@'host' IDENTIFIED BY 'password'
    [REQUIRE SSL | X509] [PASSWORD EXPIRE INTERVAL n DAY]
    [FAILED_LOGIN_ATTEMPTS n PASSWORD_LOCK_TIME n];
ALTER USER ...; RENAME USER ...; DROP USER ...;
GRANT priv_list ON level TO account [WITH GRANT OPTION];
REVOKE priv_list ON level FROM account;
CREATE ROLE 'role'; GRANT 'role' TO account; SET DEFAULT ROLE ALL TO account;
SHOW GRANTS FOR account [USING role];

Example

28.1 Role-based access design Roles are inactive in a session unless set as default (SET DEFAULT ROLE) or activated with SET ROLE; activate_all_roles_on_login = ON activates all granted roles automatically.

28.2 Privilege levels Important privileges:

SELECT, INSERT, UPDATE, DELETE CREATE, ALTER, DROP, INDEX, REFERENCES

EXECUTE, CREATE ROUTINE, ALTER ROUTINE TRIGGER, EVENT, CREATE VIEW, SHOW VIEW See all threads (SHOW PROCESSLIST) RELOAD, SHUTDOWN

REPLICATION SLAVE, REPLICATION CLIENT LOAD DATA INFILE, SELECT... INTO OUTFILE on the server

SUPER (deprecated) / dynamic privileges like SYSTEM_VARIABLES_ADMIN, CONNECTION_ADMIN, BACKUP_ADMIN Fine-grained admin tasks (8.0) ALL PRIVILEGES

Everything at that level except GRANT OPTION 28.3 Revoking and removing access

Before dropping a user, check for objects where it is the DEFINER (views, routines, triggers, events), or they will fail.

28.4 Password policies Dual passwords (8.0.14+) allow rotation without downtime:

28.5 Encrypted connections (TLS) Clients: mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=ca.pem... verifies the server certificate (protects against man-in-the-middle).

28.6 Encryption at rest and other protections InnoDB tablespace encryption (keyring component/plugin): ALTER TABLE patients ENCRYPTION = 'Y'; and default_table_encryption; redo/undo/binlog encryption options.

Enterprise features: MySQL Enterprise Audit, Firewall, Data Masking and De-identification, Transparent Data Encryption with KMIP/OCI Vault keyrings. Community alternatives include Percona's audit plugin.

local_infile = OFF unless needed; secure_file_priv restricts file import/export directories.

Remove anonymous accounts and the test database (mysql_secure_installation).

Keep MySQL patched.

28.7 Principle of least privilege checklist

-- Roles
CREATE ROLE 'r_app_rw', 'r_reporting', 'r_migrations';

GRANT SELECT, INSERT, UPDATE, DELETE ON eh_company.* TO 'r_app_rw';
GRANT EXECUTE ON eh_company.* TO 'r_app_rw';                    -- call stored procedures
GRANT SELECT ON eh_company.v_order_summary TO 'r_reporting';
GRANT SELECT ON eh_company.v_employee_directory TO 'r_reporting';
GRANT ALL PRIVILEGES ON eh_company.* TO 'r_migrations';         -- DDL, only for CI/CD migrations

-- Users
CREATE USER 'orders_svc'@'10.0.1.%'  IDENTIFIED BY 'use-a-generated-secret' REQUIRE SSL;
CREATE USER 'analyst_ravi'@'%'       IDENTIFIED BY 'use-a-generated-secret'
    PASSWORD EXPIRE INTERVAL 90 DAY FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
CREATE USER 'ci_migrator'@'10.0.9.%' IDENTIFIED BY 'use-a-generated-secret';

GRANT 'r_app_rw'     TO 'orders_svc'@'10.0.1.%';
GRANT 'r_reporting'  TO 'analyst_ravi'@'%';
GRANT 'r_migrations' TO 'ci_migrator'@'10.0.9.%';
SET DEFAULT ROLE ALL TO 'orders_svc'@'10.0.1.%', 'analyst_ravi'@'%', 'ci_migrator'@'10.0.9.%';

SHOW GRANTS FOR 'analyst_ravi'@'%' USING 'r_reporting';

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.5.%';      -- global
GRANT SELECT ON eh_company.* TO 'bi'@'%';                              -- database
GRANT SELECT, UPDATE ON eh_company.orders TO 'support'@'%';            -- table
GRANT SELECT (customer_id, name, city), UPDATE (city) ON eh_company.customers TO 'support'@'%';   -- column
GRANT EXECUTE ON PROCEDURE eh_company.place_order TO 'shop'@'%';       -- routine

REVOKE UPDATE ON eh_company.orders FROM 'support'@'%';
REVOKE 'r_reporting' FROM 'analyst_ravi'@'%';
ALTER USER 'analyst_ravi'@'%' ACCOUNT LOCK;     -- disable without deleting
DROP USER 'analyst_ravi'@'%';

SELECT 'VIEW' AS type, table_schema, table_name FROM information_schema.views WHERE definer = 'analyst_ravi@%'
UNION ALL
SELECT routine_type, routine_schema, routine_name FROM information_schema.routines WHERE definer = 'analyst_ravi@%';

INSTALL COMPONENT 'file://component_validate_password';
SET PERSIST validate_password.policy = 'STRONG';        -- LOW | MEDIUM | STRONG
SET PERSIST validate_password.length = 14;
SET PERSIST default_password_lifetime = 180;            -- days; 0 = never expire
SET PERSIST password_history = 5;                       -- cannot reuse the last 5 passwords

ALTER USER 'orders_svc'@'10.0.1.%' IDENTIFIED BY 'new-secret' RETAIN CURRENT PASSWORD;
-- deploy new secret to all app servers, then:
ALTER USER 'orders_svc'@'10.0.1.%' DISCARD OLD PASSWORD;

SHOW VARIABLES LIKE 'have_ssl';                    -- YES (MySQL 8 auto-generates certificates)
SHOW STATUS LIKE 'Ssl_cipher';                      -- non-empty if the current connection uses TLS
SET PERSIST require_secure_transport = ON;          -- reject unencrypted TCP connections
ALTER USER 'orders_svc'@'10.0.1.%' REQUIRE SSL;

[ ] No application uses root
[ ] One account per application/service, restricted by host
[ ] Runtime accounts: DML (+EXECUTE) only; no DDL, no FILE, no global privileges
[ ] Migration account separate, used only by CI/CD
[ ] Read-only accounts for reporting, via views where sensitive columns exist
[ ] TLS required for remote connections
[ ] Password policy, rotation, and account locking configured
[ ] MySQL listens only on private interfaces; port 3306 not internet-facing
[ ] Periodic review of SHOW GRANTS and unused accounts

Expected Result

Logged in as analyst_ravi:

SELECT COUNT(*) FROM eh_company.v_order_summary;     -- 6
SELECT * FROM eh_company.employees;
-- ERROR 1142 (42000): SELECT command denied to user 'analyst_ravi'@'...' for table 'employees'

Common Mistakes

'user'@'%' for everything.

GRANT ALL ON *.* to applications.

Forgetting SET DEFAULT ROLE (roles granted but inactive → "access denied").

Downgrading to mysql_native_password instead of updating connectors.

Dropping users that are DEFINERs of objects.

Best Practices

Roles for privilege sets; users for identities.

Host-restricted accounts, TLS, strong password policies, account locking on failed logins.

Audit privileged activity; review grants regularly.

Interview Relevance

High: "How do you create a read-only user?", "What are roles in MySQL 8?", "What is caching_sha2_password?", "How do you force SSL?", "What is the DEFINER problem?".

Practice Questions

Create a role that can read only orders and order_items, and a user with that role.

Allow a support user to update only the status column of orders.

Force all TCP connections to use TLS and verify with Ssl_cipher.

Rotate an application password with zero downtime.