Week 9 — Database Security: Authentication & Authorization¶

'jchen'@'localhost'       -- can only connect from the server itself
'jchen'@'10.0.1.%'        -- can connect from 10.0.1.0/24 subnet
'jchen'@'%'               -- can connect from any host
'jchen'@'10.0.1.50'       -- can connect from exactly this IP
'jchen'@'webserver01.frostburg.edu'  -- exact hostname (DNS resolved at login)

-- Basic account creation
CREATE USER 'student_app'@'10.0.1.0/255.255.255.0'
    IDENTIFIED BY 'S3cur3P@ssw0rd!'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1;   -- lock for 1 day after 5 failures

-- Account with specific authentication plugin
CREATE USER 'reporting'@'10.0.2.50'
    IDENTIFIED WITH caching_sha2_password BY 'R3portPass#2024'
    REQUIRE SSL;            -- SSL/TLS connection mandatory

-- Account that never expires (service account)
CREATE USER 'etl_service'@'localhost'
    IDENTIFIED WITH mysql_native_password BY 'ETLserv!ce#99'
    PASSWORD EXPIRE NEVER
    ACCOUNT UNLOCK;

-- Modify existing user
ALTER USER 'student_app'@'10.0.1.0/255.255.255.0'
    PASSWORD EXPIRE NOW;        -- force password change at next login

ALTER USER 'reporting'@'10.0.2.50'
    ACCOUNT LOCK;               -- disable without deleting

-- Rename/move a user
RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

-- Remove user
DROP USER IF EXISTS 'temp_user'@'%';

-- Install (if not already loaded)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Or for MySQL 8.0.4+ (component-based):
INSTALL COMPONENT 'file://component_validate_password';

-- Configure policy
SET GLOBAL validate_password.policy = 'STRONG';
-- STRONG requires: length + uppercase + lowercase + digits + special chars + no dictionary words

SET GLOBAL validate_password.length          = 12;
SET GLOBAL validate_password.mixed_case_count = 2;
SET GLOBAL validate_password.number_count    = 2;
SET GLOBAL validate_password.special_char_count = 1;

-- Test a prospective password
SELECT VALIDATE_PASSWORD_STRENGTH('weak');      -- returns 0–100
SELECT VALIDATE_PASSWORD_STRENGTH('MyC0mplex!Pass#23');  -- should return 100

-- Force password expiration after 90 days (global default)
SET GLOBAL default_password_lifetime = 90;

-- Per-account expiration
ALTER USER 'instructor_portal'@'%'
    PASSWORD EXPIRE INTERVAL 60 DAY;

-- Never expire (service accounts)
ALTER USER 'etl_service'@'localhost'
    PASSWORD EXPIRE NEVER;

-- Lock account after N failed attempts, unlock after M days
ALTER USER 'web_app'@'10.0.1.%'
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME 2;    -- 2 days; UNBOUNDED = indefinite until admin unlocks

-- Manual unlock
ALTER USER 'web_app'@'10.0.1.%' ACCOUNT UNLOCK;

-- Check locked accounts
SELECT user, host, account_locked, password_expired,
       password_lifetime, password_last_changed
FROM mysql.user
WHERE account_locked = 'Y' OR password_expired = 'Y';

-- Global: applies to ALL databases (use sparingly)
GRANT PROCESS, REPLICATION SLAVE ON *.* TO 'replica_user'@'10.0.3.%';

-- Database-level
GRANT SELECT, INSERT, UPDATE, DELETE ON university.* TO 'app_user'@'10.0.1.%';

-- Table-level
GRANT SELECT ON university.students TO 'reporting'@'10.0.2.%';
GRANT SELECT ON university.courses  TO 'reporting'@'10.0.2.%';

-- Column-level (most granular — use when views aren't appropriate)
GRANT SELECT (student_id, first_name, last_name, dept_id, gpa)
    ON university.students
    TO 'limited_access'@'localhost';
-- This user CANNOT select ssn or date_of_birth even with SELECT on other columns

-- Routine-level
GRANT EXECUTE ON PROCEDURE university.get_student_transcript
    TO 'app_user'@'10.0.1.%';

-- WITH GRANT OPTION: allow user to grant their privileges to others
GRANT SELECT ON university.* TO 'dept_admin'@'localhost' WITH GRANT OPTION;

-- Revoke specific privilege
REVOKE DELETE ON university.students FROM 'app_user'@'10.0.1.%';

-- Revoke all privileges (keeps user account, removes all grants)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_contractor'@'%';

-- Verify after revoke
SHOW GRANTS FOR 'app_user'@'10.0.1.%';
-- Should no longer show DELETE on students

-- Complete removal (revoke + drop)
DROP USER 'old_contractor'@'%';

-- After manual grants table edits (rare), flush
FLUSH PRIVILEGES;
-- Note: using GRANT/REVOKE/CREATE USER automatically flushes

-- Create roles
CREATE ROLE 'university_readonly';
CREATE ROLE 'university_instructor';
CREATE ROLE 'university_registrar';
CREATE ROLE 'university_dba';

-- Grant privileges TO roles (not users)
GRANT SELECT ON university.* TO 'university_readonly';

GRANT SELECT, INSERT, UPDATE ON university.grades      TO 'university_instructor';
GRANT SELECT ON university.students    TO 'university_instructor';
GRANT SELECT ON university.courses     TO 'university_instructor';
GRANT SELECT ON university.enrollments TO 'university_instructor';

GRANT SELECT, INSERT, UPDATE, DELETE
    ON university.enrollments  TO 'university_registrar';
GRANT SELECT, INSERT, UPDATE
    ON university.students     TO 'university_registrar';
GRANT SELECT ON university.courses TO 'university_registrar';

GRANT ALL PRIVILEGES ON university.* TO 'university_dba';

-- Grant roles to users
GRANT 'university_instructor' TO 'prof_chen'@'localhost';
GRANT 'university_instructor' TO 'prof_jones'@'localhost';
GRANT 'university_registrar'  TO 'reg_staff1'@'10.0.4.%';
GRANT 'university_readonly'   TO 'reporting'@'10.0.2.%';

-- Roles can be nested (role-within-role)
GRANT 'university_readonly' TO 'university_instructor';
-- Instructors automatically inherit readonly privileges

-- Set default role(s) for a user (auto-activated at login)
SET DEFAULT ROLE 'university_instructor' TO 'prof_chen'@'localhost';

-- Or auto-activate ALL granted roles (server-wide setting)
SET GLOBAL activate_all_roles_on_login = ON;

-- In a session: manually activate a role
SET ROLE 'university_instructor';
SET ROLE ALL;    -- activate all granted roles
SET ROLE NONE;   -- deactivate all roles (drop to base user privileges)

-- Check currently active roles in this session
SELECT CURRENT_ROLE();

-- View all role assignments
SELECT FROM_USER, FROM_HOST, TO_USER, TO_HOST, WITH_ADMIN_OPTION
FROM information_schema.ROLE_EDGES
WHERE TO_USER LIKE 'prof_%';

-- Application backend user (principle of least privilege)
CREATE USER 'app_backend'@'10.0.1.0/255.255.255.0'
    IDENTIFIED WITH caching_sha2_password BY 'B@ckendP@ss!2024'
    REQUIRE SSL
    MAX_QUERIES_PER_HOUR 100000
    MAX_USER_CONNECTIONS 50;

CREATE ROLE 'app_backend_role';
GRANT SELECT, INSERT, UPDATE ON university.students    TO 'app_backend_role';
GRANT SELECT                 ON university.courses     TO 'app_backend_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON university.enrollments TO 'app_backend_role';
GRANT SELECT, INSERT, UPDATE ON university.grades      TO 'app_backend_role';
GRANT SELECT                 ON university.departments TO 'app_backend_role';

GRANT 'app_backend_role' TO 'app_backend'@'10.0.1.0/255.255.255.0';
SET DEFAULT ROLE 'app_backend_role' TO 'app_backend'@'10.0.1.0/255.255.255.0';

-- All global privileges
SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE
FROM information_schema.USER_PRIVILEGES
ORDER BY GRANTEE;

-- Database-level privileges
SELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE, IS_GRANTABLE
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = 'university'
ORDER BY GRANTEE, PRIVILEGE_TYPE;

-- Table-level privileges
SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE
FROM information_schema.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA = 'university'
ORDER BY GRANTEE, TABLE_NAME;

-- Column-level privileges
SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE
FROM information_schema.COLUMN_PRIVILEGES
WHERE TABLE_SCHEMA = 'university'
ORDER BY GRANTEE, TABLE_NAME, COLUMN_NAME;

-- Find users with dangerous global privileges
SELECT user, host, Super_priv, Grant_priv, Create_user_priv, Shutdown_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR Shutdown_priv = 'Y'
ORDER BY user;

-- Find accounts with no password (security critical!)
SELECT user, host, authentication_string, plugin
FROM mysql.user
WHERE authentication_string = ''
   OR authentication_string IS NULL;

-- Find accounts where password never expires
SELECT user, host, password_lifetime, password_last_changed
FROM mysql.user
WHERE password_lifetime IS NULL
  AND user NOT IN ('root', 'mysql.sys', 'mysql.infoschema');

-- Full grant summary report
SELECT
    GRANTEE,
    GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS global_privs
FROM information_schema.USER_PRIVILEGES
GROUP BY GRANTEE
HAVING global_privs NOT IN ('USAGE')  -- USAGE = no privileges
ORDER BY GRANTEE;

-- Verify SSL is active
SHOW VARIABLES LIKE 'have_ssl';      -- YES
SHOW VARIABLES LIKE 'ssl_cipher';    -- current cipher suite

-- Create user requiring SSL
CREATE USER 'secure_user'@'%'
    IDENTIFIED BY 'P@ssword123!'
    REQUIRE SSL;

-- Require specific cipher
CREATE USER 'pci_user'@'10.0.5.%'
    IDENTIFIED BY 'PCIsecure1!'
    REQUIRE CIPHER 'ECDHE-RSA-AES256-GCM-SHA384';

-- Require client certificate (mutual TLS)
CREATE USER 'mtls_user'@'%'
    IDENTIFIED BY 'mTLSpass!'
    REQUIRE X509;   -- client must present a valid certificate

-- Verify connection encryption for the current session
SELECT * FROM performance_schema.session_status
WHERE VARIABLE_NAME IN ('Ssl_cipher', 'Ssl_version', 'Ssl_verify_mode');

-- Limit connections per user
ALTER USER 'app_backend'@'10.0.1.%'
    MAX_CONNECTIONS_PER_HOUR 0      -- 0 = unlimited
    MAX_QUERIES_PER_HOUR     200000
    MAX_UPDATES_PER_HOUR     50000
    MAX_USER_CONNECTIONS     100;   -- max simultaneous connections

-- Global connection settings (my.cnf)
-- max_connections = 500          (total server connections)
-- max_user_connections = 100     (per-user global default)

-- PostgreSQL: schema-level privilege model
GRANT USAGE ON SCHEMA university TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA university TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA university TO app_user;

-- Default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA university
    GRANT SELECT ON TABLES TO reporting_role;

-- Row-Level Security (RLS) — PostgreSQL 9.5+
-- Each student can only see their own enrollment records

ALTER TABLE enrollments ENABLE ROW LEVEL SECURITY;

-- Policy: students see only their rows
CREATE POLICY student_sees_own_enrollments
    ON enrollments
    FOR SELECT
    USING (
        student_id = (
            SELECT student_id FROM students
            WHERE email = current_user
        )
    );

-- Instructors see enrollments for their courses
CREATE POLICY instructor_sees_course_enrollments
    ON enrollments
    FOR SELECT
    USING (
        course_id IN (
            SELECT course_id FROM courses
            WHERE instructor_email = current_user
        )
    );

-- Registrar bypasses all RLS (role with BYPASSRLS attribute)
ALTER ROLE registrar_role BYPASSRLS;

DELIMITER //
CREATE PROCEDURE get_student_transcript(
    IN p_student_id   INT,
    IN p_semester     CHAR(6)
)
BEGIN
    -- Input validation
    IF p_student_id <= 0 OR p_semester NOT REGEXP '^[0-9]{4}(FA|SP|SU)$' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Invalid input parameters';
    END IF;

    SELECT
        s.student_id,
        s.first_name,
        s.last_name,
        c.course_code,
        c.title,
        e.grade
    FROM students s
    JOIN enrollments e ON s.student_id = e.student_id
    JOIN courses     c ON e.course_id  = c.course_id
    WHERE s.student_id = p_student_id
      AND e.semester   = p_semester;
END //
DELIMITER ;

-- Grant only EXECUTE on the procedure, not SELECT on tables
GRANT EXECUTE ON PROCEDURE university.get_student_transcript
    TO 'portal_app'@'10.0.1.%';
REVOKE ALL ON university.students FROM 'portal_app'@'10.0.1.%';
REVOKE ALL ON university.enrollments FROM 'portal_app'@'10.0.1.%';

-- 1. Application account should have ONLY necessary privileges
SHOW GRANTS FOR 'portal_app'@'10.0.1.%';

-- 2. Verify no account can run arbitrary DDL
SELECT user, host, Create_priv, Drop_priv, Alter_priv
FROM mysql.user WHERE user = 'portal_app';
-- All should be 'N'

-- 3. Enable MySQL general query log temporarily to inspect all queries
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
-- Review for suspicious patterns, then turn off (performance impact)
SET GLOBAL general_log = 'OFF';

-- 4. Create a read-only proxy view for the application
-- If the app only reads students, use a view and only grant SELECT on the view