Chapter 8: Security and User Management

User Account Management

Creating Users

-- Create user with password
CREATE USER 'john'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Create user accessible from any host
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass456!';

-- Create user from specific IP
CREATE USER 'admin'@'192.168.1.100' IDENTIFIED BY 'AdminPass789!';

Modifying Users

-- Change password
ALTER USER 'john'@'localhost' IDENTIFIED BY 'NewPass123!';

-- Rename user
RENAME USER 'john'@'localhost' TO 'john_doe'@'localhost';

-- Drop user
DROP USER 'john_doe'@'localhost';

Privileges

Granting Privileges

-- Grant all privileges on database
GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.customers TO 'app_user'@'%';

-- Grant column-level privileges
GRANT SELECT (name, email), UPDATE (email) ON mydb.users TO 'support'@'localhost';

-- Grant with grant option
GRANT SELECT ON mydb.* TO 'manager'@'localhost' WITH GRANT OPTION;

-- Apply changes
FLUSH PRIVILEGES;

Revoking Privileges

-- Revoke specific privileges
REVOKE INSERT, DELETE ON mydb.* FROM 'app_user'@'%';

-- Revoke all privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'john'@'localhost';

Viewing Privileges

-- Show user privileges
SHOW GRANTS FOR 'john'@'localhost';

-- Current user privileges
SHOW GRANTS FOR CURRENT_USER();

-- All users
SELECT user, host FROM mysql.user;

Roles (MySQL 8.0+)

-- Create role
CREATE ROLE 'app_read', 'app_write';

-- Grant privileges to role
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';

-- Assign role to user
GRANT 'app_read' TO 'john'@'localhost';
GRANT 'app_read', 'app_write' TO 'admin'@'localhost';

-- Activate roles
SET DEFAULT ROLE ALL TO 'john'@'localhost';

Password Security

Password Validation Plugin

-- Install plugin
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Configure password policy
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 2;
SET GLOBAL validate_password.special_char_count = 2;

Password Expiration

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

-- Force password change
ALTER USER 'john'@'localhost' PASSWORD EXPIRE;

SSL/TLS Connections

-- Require SSL for user
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'pass' REQUIRE SSL;

-- Require specific certificate
CREATE USER 'cert_user'@'%' IDENTIFIED BY 'pass'
REQUIRE SUBJECT '/CN=client/O=MyOrg';

-- Check SSL status
SHOW VARIABLES LIKE '%ssl%';

Data Encryption

Transparent Data Encryption (TDE)

-- Enable encryption for new tables
ALTER INSTANCE ROTATE INNODB MASTER KEY;

-- Create encrypted table
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    data VARCHAR(255)
) ENCRYPTION='Y';

Column Encryption

-- Using AES encryption
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    password_hash VARCHAR(255),
    ssn VARBINARY(255)
);

-- Insert encrypted data
INSERT INTO users (username, password_hash, ssn)
VALUES ('john', SHA2('password', 256), AES_ENCRYPT('123-45-6789', 'secret_key'));

-- Retrieve decrypted data
SELECT username, AES_DECRYPT(ssn, 'secret_key') AS ssn
FROM users;

Auditing

Audit Plugin

-- Install audit plugin
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- Configure audit log
SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';

Manual Audit Tables

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user VARCHAR(50),
    action VARCHAR(50),
    table_name VARCHAR(50),
    record_id INT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    details JSON
);

-- Trigger for auditing
CREATE TRIGGER audit_customer_changes
AFTER UPDATE ON customers
FOR EACH ROW
INSERT INTO audit_log (user, action, table_name, record_id, details)
VALUES (USER(), 'UPDATE', 'customers', NEW.id, JSON_OBJECT('old', OLD.email, 'new', NEW.email));

SQL Injection Prevention

Use Prepared Statements

-- Prepared statement example
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ? AND password = ?';
SET @user = 'john';
SET @pass = 'password';
EXECUTE stmt USING @user, @pass;
DEALLOCATE PREPARE stmt;

Input Validation

• Always validate input in application layer
• Use parameterized queries
• Escape special characters
• Limit input length
• Use stored procedures where appropriate

Security Best Practices

1. Principle of Least Privilege: Grant only necessary permissions
2. Strong Passwords: Enforce complexity requirements
3. Regular Updates: Keep MySQL server updated
4. Remove Default Accounts: Delete test users and databases
5. Network Security: Use firewalls and VPNs
6. Backup Encryption: Encrypt backup files
7. Monitor Access: Review logs regularly
8. Separate Environments: Don't use production credentials in development

---

Next: Chapter 9: Backup and Recovery