Database Security và Administration

User Management

Tạo User

-- Tạo user mới
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'readonly_pass';

Xóa User

-- Xóa user
DROP USER 'old_user'@'localhost';

Privilege Management

Grant Privileges

-- Cấp quyền cơ bản
GRANT SELECT, INSERT, UPDATE ON ecommerce.* TO 'app_user'@'localhost';

-- Cấp quyền cho specific table
GRANT SELECT ON ecommerce.products TO 'readonly_user'@'%';

-- Cấp tất cả quyền
GRANT ALL PRIVILEGES ON ecommerce.* TO 'admin_user'@'localhost';

Revoke Privileges

-- Thu hồi quyền
REVOKE DELETE ON ecommerce.* FROM 'app_user'@'localhost';

Xem Privileges

-- Xem quyền của user
SHOW GRANTS FOR 'app_user'@'localhost';

Role-based Access Control

Tạo Roles

-- Tạo roles
CREATE ROLE 'order_manager';
CREATE ROLE 'product_manager';
CREATE ROLE 'report_viewer';

Gán Privileges cho Roles

-- Cấp quyền cho roles
GRANT SELECT, INSERT, UPDATE ON ecommerce.orders TO 'order_manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce.products TO 'product_manager';
GRANT SELECT ON ecommerce.* TO 'report_viewer';

Gán Roles cho Users

-- Gán role cho user
GRANT 'order_manager' TO 'user1'@'localhost';
GRANT 'product_manager' TO 'user2'@'localhost';
GRANT 'report_viewer' TO 'user3'@'localhost';

-- Kích hoạt role
SET DEFAULT ROLE ALL TO 'user1'@'localhost';

Database Backup và Recovery

Logical Backup với mysqldump

# Backup toàn bộ database
mysqldump -u root -p ecommerce > ecommerce_backup.sql

# Backup specific tables
mysqldump -u root -p ecommerce orders order_items > orders_backup.sql

# Backup với transaction consistent
mysqldump -u root -p --single-transaction ecommerce > ecommerce_consistent.sql

Restore từ Backup

# Restore database
mysql -u root -p ecommerce < ecommerce_backup.sql

Automated Backups

-- Event scheduler cho automatic backup (MySQL)
CREATE EVENT daily_backup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
  -- Logic backup có thể implement qua stored procedure
  CALL create_daily_backup();
END;

Security Best Practices

Password Policies

-- Thiết lập password policy (MySQL 8.0+)
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;

Audit Logging

-- Tạo audit table
CREATE TABLE audit_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(100),
  action_type VARCHAR(50),
  table_name VARCHAR(100),
  record_id INT,
  old_values JSON,
  new_values JSON,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ip_address VARCHAR(45)
);

-- Trigger cho audit logging
CREATE TRIGGER audit_products_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (user_name, action_type, table_name, record_id, old_values, new_values)
  VALUES (USER(), 'UPDATE', 'products', NEW.product_id, 
          JSON_OBJECT('product_name', OLD.product_name, 'price', OLD.price),
          JSON_OBJECT('product_name', NEW.product_name, 'price', NEW.price));
END;

Data Encryption

-- Encryption at rest (MySQL)
CREATE TABLE sensitive_data (
  id INT PRIMARY KEY,
  credit_card_number VARBINARY(255),
  ssn VARBINARY(255)
);

-- Insert encrypted data
INSERT INTO sensitive_data (id, credit_card_number, ssn)
VALUES (1, 
        AES_ENCRYPT('4111111111111111', 'encryption_key'),
        AES_ENCRYPT('123-45-6789', 'encryption_key'));

-- Select decrypted data
SELECT id, 
       AES_DECRYPT(credit_card_number, 'encryption_key') as credit_card,
       AES_DECRYPT(ssn, 'encryption_key') as ssn
FROM sensitive_data;

Performance Monitoring

Monitoring Queries

-- Bật slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Xem process list
SHOW PROCESSLIST;

-- Xem thông tin performance
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Queries';
SHOW STATUS LIKE 'Slow_queries';

Index Usage Statistics

-- Xem index usage (MySQL)
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'ecommerce';

-- Xem unused indexes
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'ecommerce';