MySQL Security & Permissions Interview Questions

Password storage: Hashed in mysql.user table. MySQL 8.0 uses caching_sha2_password (SHA-256). Older versions use mysql_native_password. Support for role-based login.

-- Check password plugin
SELECT user, host, plugin FROM mysql.user;
-- Result: plugin = 'caching_sha2_password' (MySQL 8.0+)

-- Set password
SET PASSWORD FOR 'user'@'localhost' = 'new_password';
ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';

-- Set password with specific hash
ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

-- Password validation policy
SHOW VARIABLES LIKE 'validate_password%';
-- validate_password_policy: MEDIUM (default)
-- validate_password_length: 8 (minimum length)

-- Setup password policy
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;

-- Password expiration
ALTER USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
SELECT * FROM mysql.user WHERE password_expired = 'Y';

-- Account locking
ALTER USER 'user'@'localhost' ACCOUNT LOCK;
ALTER USER 'user'@'localhost' ACCOUNT UNLOCK;

Why it matters: Authentication security, password best practices.

Real applications: User management, compliance requirements.

Common mistakes: Weak passwords, old password plugins, no expiration policy.

Roles (MySQL 8.0+) group permissions for users. Simplify permission management for similar users. Can be nested.

-- Create role
CREATE ROLE 'app_read_only', 'app_write', 'app_admin';

-- Grant privileges to role
GRANT SELECT ON mydb.* TO 'app_read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

-- Assign role to user
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
GRANT 'app_read_only' TO 'user1'@'localhost';

-- Set default role
SET DEFAULT ROLE 'app_read_only' FOR 'user1'@'localhost';

-- User must activate role on connect
-- Add to connection or my.cnf
SET ROLE ALL;  -- Activate all assigned roles

-- View role assignments
SELECT * FROM mysql.role_edges;
SELECT CURRENT_ROLE;

-- Role hierarchy
CREATE ROLE 'developer';
GRANT 'app_read_only' TO 'developer';  -- Nested role
GRANT 'developer' TO 'user2'@'localhost';  -- User gets all permissions

Why it matters: Simplified permission management at scale.

Real applications: Multi-app environments, standardized permissions.

Common mistakes: Roles not set as default, MySQL < 8.0 not supported.

SSL/TLS encrypts connections. Prevents credential interception. Configure with server certificates and require REQUIRE SSL for users.

-- Generate SSL certificates (one time)
openssl genrsa -out ca-key.pem 4096
openssl req -new -x509 -days 365 -key ca-key.pem -out ca.pem
openssl genrsa -out server-key.pem 4096
openssl req -new -key server-key.pem -out server.csr
openssl x509 -req -days 365 -in server.csr -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem
openssl genrsa -out client-key.pem 4096
openssl req -new -key client-key.pem -out client.csr
openssl x509 -req -days 365 -in client.csr -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out client-cert.pem

-- Configure MySQL server (my.cnf)
[mysqld]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

-- Restart MySQL
systemctl restart mysql

-- Verify SSL enabled
SHOW VARIABLES LIKE 'have_ssl';  -- Result: YES

-- Require SSL for user
CREATE USER 'secuser'@'192.168.1.%' IDENTIFIED BY 'password' REQUIRE SSL;
ALTER USER 'secuser'@'192.168.1.%' REQUIRE SSL;

-- Connect with SSL
mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h servername
mysql --ssl-mode=REQUIRED -h servername

-- Verify SSL connection
SHOW STATUS LIKE 'Ssl_version';  -- Result: TLSv1.2

Why it matters: Credential and data encryption in transit.

Real applications: Remote connections, compliance (GDPR, HIPAA).

Common mistakes: Not enforcing SSL, self-signed certificates in production.

GRANT assigns permissions. REVOKE removes permissions. Privilege levels: global, database, table, column, procedure. WITH GRANT OPTION allows delegation.

-- Global privileges (*.*)
GRANT SELECT, SHOW DATABASES ON *.* TO 'user'@'host';

-- Database privileges (database.*)
GRANT CREATE, ALTER, DROP ON testdb.* TO 'user'@'host';

-- Table privileges (database.table)
GRANT SELECT, UPDATE, DELETE ON testdb.orders TO 'user'@'host';

-- Column privileges (specific columns)
GRANT UPDATE(price, discount), SELECT ON sales.products TO 'analyst'@'host';

-- Procedure/Function privileges
GRANT EXECUTE ON PROCEDURE testdb.calculate_revenue TO 'user'@'host';

-- With grant option (can delegate)
GRANT SELECT ON *.* TO 'user'@'host' WITH GRANT OPTION;

-- Revoke specific privilege
REVOKE INSERT ON testdb.* FROM 'user'@'host';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'host';

-- Revoke grant option
REVOKE GRANT OPTION ON *.* FROM 'user'@'host';

-- Common privilege sets
-- SELECT: Read data
-- INSERT: Insert new data
-- UPDATE: Modify data
-- DELETE: Remove data
-- ALL: All privileges (except WITH GRANT OPTION)
-- USAGE: No access

-- Application user (principle of least privilege)
GRANT SELECT, INSERT, UPDATE ON appdb.* TO 'app'@'app-server' IDENTIFIED BY 'password';

Why it matters: Enforce least privilege principle.

Real applications: Application users, analyst access, contractor accounts.

Common mistakes: ALL PRIVILEGES to app users, not revoking stale privileges.

Auditing tracks user actions via general log, audit plugin, or application logging. Important for compliance and security investigations.

-- Enable general query log
SET GLOBAL general_log = ON;
SET GLOBAL log_output = 'TABLE';  -- Log to mysql.general_log table

-- Query log (includes connection, disconnection, queries)
SELECT * FROM mysql.general_log WHERE user = 'appuser' ORDER BY event_time DESC LIMIT 10;

-- Disable query log (verbose)
SET GLOBAL general_log = OFF;

-- MySQL Audit Plugin
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_events = 'Connect,Query_ddl,Query_dml';

-- Slow query log (for query analysis)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- Queries > 2 seconds
SELECT * FROM mysql.slow_log;

-- Application-level audit table
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user VARCHAR(100),
    action VARCHAR(50),
    table_name VARCHAR(100),
    record_id INT,
    old_value TEXT,
    new_value TEXT,
    timestamp DATETIME DEFAULT NOW()
);

-- Trigger for audit
CREATE TRIGGER orders_update_audit AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO audit_log (user, action, table_name, record_id, old_value, new_value)
VALUES (USER(), 'UPDATE', 'orders', NEW.id, JSON_OBJECT('status', OLD.status), JSON_OBJECT('status', NEW.status));

Why it matters: Compliance, threat detection, investigation.

Real applications: Security audits, PCI-DSS compliance, forensics.

Common mistakes: Not logging to persistent storage, performance impact ignored.

SQL injection: Unsanitized input. Default credentials: Weak passwords. Privilege escalation: Overpermissive grants. Unencrypted transport: No SSL/TLS. Exposed backups: World-readable dump files.

-- SQL Injection vulnerability
// Vulnerable code
SELECT * FROM users WHERE username = '$username' AND password = '$password';
// Injection: $username = "admin' --"

-- Prevention: Use prepared statements
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ? AND password = ?';
SET @u = 'user', @p = 'pass';
EXECUTE stmt USING @u, @p;

-- Default credentials vulnerability
-- Change root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_password';

-- Delete anonymous users
DROP USER ''@'localhost';
DROP USER ''@'%.%.%.%';

-- Privilege escalation
-- Don't grant too much
GRANT ALL ON *.* TO 'app'@'app-server';  -- BAD
GRANT SELECT ON db.* TO 'app'@'app-server';  -- GOOD

-- Unencrypted transport
-- Require SSL
ALTER USER 'user'@'host' REQUIRE SSL;

-- Exposed backups
-- Restrict permissions on dump files
chmod 600 backup.sql
# Encrypt backups
gpg --encrypt backup.sql

-- Data exposure
-- Don't select * in logs
SELECT user, host FROM mysql.user;  -- DON'T SELECT password!
SELECT id, amount FROM orders;  -- DON'T SELECT credit_card_number!

Why it matters: Prevent data breaches and unauthorized access.

Real applications: Secure application design, regulatory compliance.

Common mistakes: No input validation, weak passwords, overpermissive access.

Best practices: Least privilege principle, strong passwords, SSL/TLS, regular audits, principle of separation, backup security, update regularly.

-- ✅ Good: Least privilege for applications
CREATE USER 'webdb'@'web-server' IDENTIFIED BY 'long_random_password';
GRANT SELECT, INSERT, UPDATE ON webdb.* TO 'webdb'@'web-server';
-- NO DELETE, NO ADMIN PRIVILEGES

-- ✅ Good: Separate users by role
CREATE USER 'app'@'app-host';  -- Application
CREATE USER 'backup'@'backup-host';  -- Backup jobs
CREATE USER 'analytics'@'analytics-host';  -- Read-only analytics
-- Each with minimal required permissions

-- ✅ Good: Strong password policy
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;

-- ✅ Good: Secure connections
ALTER USER 'remote-user'@'%.domain.com' REQUIRE SSL;
CREATE USER 'local-admin'@'localhost' IDENTIFIED BY 'pass';  -- Local is OK

-- ✅ Good: Regular audits
SELECT user, host, plugin FROM mysql.user;  -- Review users
SHOW GRANTS FOR 'appuser'@'%';  -- Verify permissions

-- ❌ Bad: Root access for applications
GRANT ALL ON *.* TO 'app'@'app-host';  -- Dangerous!

-- ❌ Bad: Weak passwords
CREATE USER 'user'@'host' IDENTIFIED BY '123';  -- Weak!

-- ❌ Bad: Exposed backups
mysqldump > backup.sql  # Readable by all!
chmod 644 backup.sql  # World readable!

-- ✅ Good: Encrypted backups
mysqldump | gpg -e > backup.sql.gpg
chmod 600 backup.sql.gpg

Why it matters: Prevent data breaches and compliance violations.

Real applications: Production systems, regulated industries.

Common mistakes: Overpermissive access, weak passwords, unencrypted backups.