Week 10 — Database Security: Encryption, Auditing & Compliance¶

-- Check if encryption is supported and keyring is loaded
SHOW VARIABLES LIKE 'have_ssl';
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_NAME LIKE 'keyring%';

-- For community edition: install keyring_file plugin
-- In my.cnf (must be set before server starts):
-- [mysqld]
-- early-plugin-load = keyring_file.so
-- keyring_file_data = /var/lib/mysql-keyring/keyring

-- Verify keyring is loaded
SELECT * FROM performance_schema.keyring_keys;

-- Encrypt a table at creation time
CREATE TABLE grades_encrypted (
    grade_id    INT           NOT NULL AUTO_INCREMENT,
    student_id  INT           NOT NULL,
    course_id   INT           NOT NULL,
    points      DECIMAL(5,2),
    max_points  DECIMAL(5,2),
    graded_on   DATE,
    PRIMARY KEY (grade_id)
) ENCRYPTION = 'Y';

-- Encrypt an existing table (online operation)
ALTER TABLE grades ENCRYPTION = 'Y';

-- Verify encryption status
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'university'
  AND CREATE_OPTIONS LIKE '%ENCRYPTION%';

-- Rotate the master encryption key (re-encrypts table encryption keys, NOT data)
ALTER INSTANCE ROTATE INNODB MASTER KEY;

-- This is safe and fast — table data is not re-encrypted;
-- only the table encryption key (TEK) wrapping is updated

-- Verify TLS status on a live connection
SHOW STATUS LIKE 'Ssl_%';
-- Ssl_cipher:     ECDHE-RSA-AES256-GCM-SHA384
-- Ssl_version:    TLSv1.3
-- Ssl_verify_mode: 5 (CA + hostname verified)

-- Check if the current connection is TLS-encrypted
SELECT CONNECTION_ID(),
       VARIABLE_VALUE AS tls_version
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Ssl_version';

-- Configuration: use AES-256-CBC (set block_encryption_mode)
SET GLOBAL block_encryption_mode = 'aes-256-cbc';

-- Storing encrypted PII
-- IMPORTANT: use a fixed initialization vector OR store IV alongside ciphertext
-- For production, generate a random IV per row and store it

ALTER TABLE students
    ADD COLUMN ssn_encrypted VARBINARY(256),  -- larger than CHAR to hold ciphertext
    ADD COLUMN ssn_iv        VARBINARY(16);   -- store IV with ciphertext

-- Insert with encryption (app should generate the key from a secure KMS, NOT hardcoded)
SET @encryption_key = SHA2('my-secret-key-from-vault', 256);

UPDATE students SET
    ssn_encrypted = AES_ENCRYPT('123-45-6789', @encryption_key),
    ssn = NULL       -- remove plaintext after encryption
WHERE student_id = 1001;

-- Decrypt for authorized queries
SELECT
    student_id,
    AES_DECRYPT(ssn_encrypted, @encryption_key) AS ssn_plaintext
FROM students
WHERE student_id = 1001;

-- Column-level key rotation (app-managed):
-- 1. Add a new column with the new key version
ALTER TABLE students ADD COLUMN ssn_encrypted_v2 VARBINARY(256);

-- 2. Re-encrypt all rows in batches (avoid locking)
SET @old_key = SHA2('old-key-from-vault', 256);
SET @new_key = SHA2('new-key-from-vault', 256);

UPDATE students
SET ssn_encrypted_v2 = AES_ENCRYPT(
        AES_DECRYPT(ssn_encrypted, @old_key),
        @new_key
    )
WHERE student_id BETWEEN 1 AND 1000;   -- batch to avoid long-running transaction

-- 3. Verify batch, then continue...
-- 4. After all rows: swap column names and drop old column
-- 5. Rotate key in secrets manager; invalidate old key

-- Configure audit log
SET GLOBAL audit_log_format           = 'JSON';
SET GLOBAL audit_log_policy           = 'ALL';
SET GLOBAL audit_log_rotate_on_size   = 100 * 1024 * 1024;  -- rotate at 100 MB
SET GLOBAL audit_log_flush            = ON;

-- Filter: audit only sensitive database operations
SELECT audit_log_filter_set_filter('log_sensitive',
'{ "filter": {
    "class": [
      { "name": "connection" },
      { "name": "table_access",
        "event": [
          {"name": "read",   "log": { "field": { "name": "table.name",
                                                  "value": "students" }}},
          {"name": "insert", "log": true},
          {"name": "delete", "log": true},
          {"name": "update", "log": true}
        ]
      }
    ]
  }
}');

SELECT audit_log_filter_set_user('%', 'log_sensitive');

-- Audit log table: append-only, no DELETE privilege granted
CREATE TABLE audit_log (
    audit_id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_time     DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    db_user        VARCHAR(100)    NOT NULL DEFAULT (CURRENT_USER()),
    action_type    ENUM('INSERT','UPDATE','DELETE','DDL') NOT NULL,
    table_name     VARCHAR(64)     NOT NULL,
    record_id      BIGINT,          -- PK of the affected row
    old_values     JSON,            -- before state (UPDATE/DELETE)
    new_values     JSON,            -- after state (INSERT/UPDATE)
    client_ip      VARCHAR(45)      DEFAULT NULL,
    application    VARCHAR(100)     DEFAULT NULL,
    PRIMARY KEY (audit_id),
    INDEX idx_audit_time  (event_time),
    INDEX idx_audit_table (table_name, event_time),
    INDEX idx_audit_user  (db_user, event_time)
) ENGINE = InnoDB;

-- Prevent tampering: revoke DELETE and UPDATE from all users
REVOKE DELETE, UPDATE ON university.audit_log FROM 'app_user'@'%';
REVOKE DELETE, UPDATE ON university.audit_log FROM 'university_dba'@'%';
-- Only a dedicated audit_writer role can INSERT

DELIMITER //

-- Audit INSERT on students
CREATE TRIGGER trg_students_after_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action_type, table_name, record_id, new_values)
    VALUES (
        'INSERT',
        'students',
        NEW.student_id,
        JSON_OBJECT(
            'student_id',  NEW.student_id,
            'first_name',  NEW.first_name,
            'last_name',   NEW.last_name,
            'email',       NEW.email,
            'dept_id',     NEW.dept_id,
            'gpa',         NEW.gpa,
            'enrolled_on', NEW.enrolled_on,
            'is_active',   NEW.is_active
        )
    );
END //

-- Audit UPDATE on students (captures before AND after state)
CREATE TRIGGER trg_students_after_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    -- Only log if something actually changed (avoid spurious entries)
    IF NOT (
        OLD.first_name <=> NEW.first_name AND
        OLD.last_name  <=> NEW.last_name  AND
        OLD.email      <=> NEW.email      AND
        OLD.gpa        <=> NEW.gpa        AND
        OLD.dept_id    <=> NEW.dept_id    AND
        OLD.is_active  <=> NEW.is_active
    ) THEN
        INSERT INTO audit_log (action_type, table_name, record_id, old_values, new_values)
        VALUES (
            'UPDATE',
            'students',
            NEW.student_id,
            JSON_OBJECT(
                'first_name', OLD.first_name,
                'last_name',  OLD.last_name,
                'email',      OLD.email,
                'gpa',        OLD.gpa,
                'dept_id',    OLD.dept_id,
                'is_active',  OLD.is_active
            ),
            JSON_OBJECT(
                'first_name', NEW.first_name,
                'last_name',  NEW.last_name,
                'email',      NEW.email,
                'gpa',        NEW.gpa,
                'dept_id',    NEW.dept_id,
                'is_active',  NEW.is_active
            )
        );
    END IF;
END //

-- Audit DELETE on students (capture full row before deletion)
CREATE TRIGGER trg_students_before_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action_type, table_name, record_id, old_values)
    VALUES (
        'DELETE',
        'students',
        OLD.student_id,
        JSON_OBJECT(
            'student_id',  OLD.student_id,
            'first_name',  OLD.first_name,
            'last_name',   OLD.last_name,
            'email',       OLD.email,
            'ssn_hint',    CONCAT('***-**-', RIGHT(OLD.ssn, 4)),
            'gpa',         OLD.gpa,
            'dept_id',     OLD.dept_id,
            'is_active',   OLD.is_active
        )
    );
END //

DELIMITER ;

-- Capture schema changes by comparing snapshots
CREATE TABLE schema_snapshot (
    snapshot_id   BIGINT UNSIGNED AUTO_INCREMENT,
    captured_at   DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    table_schema  VARCHAR(64),
    table_name    VARCHAR(64),
    create_time   DATETIME,
    update_time   DATETIME,
    table_rows    BIGINT,
    PRIMARY KEY (snapshot_id),
    INDEX idx_snap_schema (table_schema, table_name)
);

-- Event to detect DDL changes (runs every 5 minutes)
CREATE EVENT ev_detect_ddl_changes
ON SCHEDULE EVERY 5 MINUTE
DO
    INSERT INTO schema_snapshot (table_schema, table_name, create_time, update_time, table_rows)
    SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_TIME, UPDATE_TIME, TABLE_ROWS
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'university';

-- HIPAA: every user accessing PHI must be individually identified
-- NO shared accounts like 'clinic_app'@'%' that multiple people use

-- Create individual accounts for each staff member
CREATE USER 'nurse_johnson'@'clinic-ws-01.frostburg.edu'
    IDENTIFIED WITH caching_sha2_password BY '...'
    REQUIRE SSL
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME UNBOUNDED;

-- Audit all access to health records
CREATE TRIGGER trg_health_records_select
-- MySQL doesn't support SELECT triggers; use audit plugin or app-layer audit instead

-- PCI: Store only last 4 digits (truncation — allowed alternative to encryption)
ALTER TABLE payment_records
    ADD COLUMN card_last_four CHAR(4),   -- '4242'
    ADD COLUMN card_brand     VARCHAR(20); -- 'VISA', 'MC'
-- Never store full PAN in database at all; process via payment gateway tokenization

-- If full PAN must be stored (rare): encrypt with AES-256
ALTER TABLE payment_records
    ADD COLUMN pan_encrypted VARBINARY(256),
    ADD COLUMN pan_key_version TINYINT;   -- track which key version was used