Data Governance & Security (Masking, RBAC, Audit Logging)

Status: planned, not started. The commands below are the plan. None of it has run yet.

Audit and tighten who can do what in the databases, mask personal data for people who don't need it, and log every change made through this site's own admin panel with the real person behind it. The aim is to govern a system that actually exists, not a synthetic exercise.

Tools (planned)

Tool	Will be used for
MySQL 8.4 grants, roles, SHOW GRANTS	Least-privilege audit and role-based access
Views (or ProxySQL query rewrite as a comparison)	Dynamic column masking per role
Triggers + an audit_log table	Change history for the admin write path
This site's admin API (Next.js)	Passing the signed-in Tailscale identity into the audit log
ProxySQL query log	Who ran which statement through the proxy

Plan

1. Grant audit across every account

What it will do: list every user and their grants, and flag anything broader than its job needs. There are about 15 accounts now: app users, the pipeline, monitoring, replication, Debezium, Orchestrator, ProxySQL's monitor.

SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';')
FROM mysql.user WHERE user NOT LIKE 'mysql.%';

Success check: a report of findings, and every finding fixed, not just noted.

2. Roles instead of per-user grants

What it will do: define roles such as app_read, app_write and pipeline_write, and grant those to users.

CREATE ROLE 'app_read';
GRANT SELECT ON portfolio.* TO 'app_read';
GRANT 'app_read' TO 'portfolio_web'@'%';
SET DEFAULT ROLE ALL TO 'portfolio_web'@'%';

3. Column masking

What it will do: apply masking to a real personal-data candidate (or a clearly labeled synthetic one), so a reader role sees j***@example.com while the owner sees the real value.

CREATE VIEW contacts_masked AS
SELECT id, CONCAT(LEFT(email, 1), '***', SUBSTRING(email, LOCATE('@', email))) AS email
FROM contacts;
GRANT SELECT ON contacts_masked TO 'app_read';   -- no grant on the base table

Success check: the same query run as two different roles returns masked vs. clear values.

4. Audit log for the admin panel

What it will do:

• Add AFTER INSERT/UPDATE triggers on projects, blog_posts, skill_categories and skill_groups, writing old and new values to audit_log.
• Have the admin API set @actor from the Tailscale-User-Login header, so every row names a real person.

CREATE TRIGGER projects_audit AFTER UPDATE ON projects FOR EACH ROW
INSERT INTO audit_log (tbl, row_id, actor, old_value, new_value, at)
VALUES ('projects', NEW.id, @actor,
        JSON_OBJECT('title', OLD.title, 'status', OLD.progress_status),
        JSON_OBJECT('title', NEW.title, 'status', NEW.progress_status), NOW());

5. Write the policy down

What it will do: a short governance document covering who can read and write what, why, and how each rule is enforced, tied to the real grants from step 1.

Known issues to plan around

• Replication of triggers: triggers fire on the primary, and their writes replicate as row events (no double-logging). This needs confirming on all three servers and in CDC.
• Connection-scoped @actor: ProxySQL reuses connections, so a session variable can leak between requests. Set it in the same statement or transaction as the write.
• Restore before testing: the incident in the HA project came from accounts missing on one server, so any grant changes must be checked on all three servers before restarting apps.