Auditing, Logging and Protecting Personal Data

Two questions come up in every serious system: “who did what and when?” (auditing) and “how do we protect people’s data?” (privacy). MySQL gives you several building blocks. Here is what each can and cannot do.

What MySQL logs by default

The general log: possible, but blunt

1

SELECT @@general_log AS general_log_on, @@log_output AS output_to;

1
2
3
4
5
6

+----------------+-----------+
| general_log_on | output_to |
+----------------+-----------+
|              0 | FILE      |
+----------------+-----------+
1 row in set

Turning on the general log writes every statement (including SELECTs and the text of literals) to a file or table. It slows the server, grows quickly, and contains personal data and secrets in the statement text. Use it for short debugging sessions, not as an audit trail. SET GLOBAL general_log = 'ON' and OFF again after a few minutes.

The binary log: what changed

The binary log records changes, with timestamps and the connection id, but not who in application terms, and not SELECTs. With binlog_format = ROW you can see the old and new values with mysqlbinlog --verbose. It helps to reconstruct an incident (Point-in-Time Recovery).

Audit plugins

An audit log records connections, failed logins and statements, in a form that compliance auditors accept:

• MySQL Enterprise Audit: part of the commercial MySQL Enterprise Edition.
• Percona Audit Log Plugin (Percona Server for MySQL): open-source, compatible with the Enterprise format.
• MariaDB Audit Plugin: also usable with some MySQL builds.
• Cloud services (RDS, Cloud SQL, Azure) offer audit logging as a managed option.

The MySQL Community Edition does not include an audit log. If you need one, choose one of the alternatives, or build application-level auditing.

Trigger-based audit tables

A common and portable approach is an audit table filled by triggers (Triggers):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(50) NOT NULL, credit_limit INT NOT NULL);
CREATE TABLE customers_audit (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    changed_by VARCHAR(100) NOT NULL,
    action CHAR(6) NOT NULL,
    customer_id INT NOT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    KEY idx_customer (customer_id, changed_at)
);

DELIMITER //

CREATE TRIGGER trg_customers_audit_update AFTER UPDATE ON customers FOR EACH ROW
BEGIN
    INSERT INTO customers_audit (changed_by, action, customer_id, old_values, new_values)
    VALUES (CURRENT_USER(), 'UPDATE', OLD.id,
            JSON_OBJECT('name', OLD.name, 'email', OLD.email, 'credit_limit', OLD.credit_limit),
            JSON_OBJECT('name', NEW.name, 'email', NEW.email, 'credit_limit', NEW.credit_limit));
END//

CREATE TRIGGER trg_customers_audit_delete AFTER DELETE ON customers FOR EACH ROW
BEGIN
    INSERT INTO customers_audit (changed_by, action, customer_id, old_values)
    VALUES (CURRENT_USER(), 'DELETE', OLD.id, JSON_OBJECT('name', OLD.name, 'email', OLD.email, 'credit_limit', OLD.credit_limit));
END//

DELIMITER ;

INSERT INTO customers VALUES (1, 'Maya', 'maya@example.com', 1000), (2, 'Rohan', 'rohan@example.com', 500);
UPDATE customers SET credit_limit = 5000 WHERE id = 1;
DELETE FROM customers WHERE id = 2;

SELECT customer_id, action, changed_by, old_values->>'$.credit_limit' AS old_limit, new_values->>'$.credit_limit' AS new_limit FROM customers_audit ORDER BY id;

1
2
3
4
5
6
7

+-------------+--------+----------------+-----------+-----------+
| customer_id | action | changed_by     | old_limit | new_limit |
+-------------+--------+----------------+-----------+-----------+
|           1 | UPDATE | root@localhost | 1000      | 5000      |
|           2 | DELETE | root@localhost | 500       | NULL      |
+-------------+--------+----------------+-----------+-----------+
2 rows in set

Each change records who (the database account, CURRENT_USER()), when, the action and the old and new values. Limits to remember:

• CURRENT_USER() is the database account (shop_app@...), not the person. The application should set a session variable (for example SET @app_user_id = 42) that the trigger also reads, to record the real user.
• Triggers do not fire for TRUNCATE, and not for cascaded foreign key actions.
• Anyone who can change the audit table can falsify the audit. Give the application account INSERT on it only (the trigger runs as the definer), and no UPDATE or DELETE.

Application-level audit

Many applications write audit entries themselves: who clicked what, in business terms (“user 42 approved refund 981”). It captures the meaning the database cannot know. Use it together with database-level logging for critical data.

Protecting personal data

Personal data (names, emails, phone numbers, addresses, IDs, health and financial details, IP addresses) is regulated in many countries (GDPR in the EU, DPDP in India, CCPA in California, and others). The technical measures below help, and they do not replace legal advice.

1. Collect less

The safest personal data is the data you never stored. Keep only what you need, and for as long as you need it.

2. Limit who can see it

Use column privileges or views to hide sensitive columns from reports and support staff (GRANT, Views):

1
2
3
4
5
6
7
8

CREATE VIEW customers_masked AS
SELECT id,
       name,
       CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email, '@', -1)) AS email_masked,
       'hidden' AS credit_limit
FROM customers;

SELECT * FROM customers_masked ORDER BY id;

1
2
3
4
5
6

+----+------+-------------------+--------------+
| id | name | email_masked      | credit_limit |
+----+------+-------------------+--------------+
|  1 | Maya | ma***@example.com | hidden       |
+----+------+-------------------+--------------+
1 row in set

An analyst with access to the view only sees masked values.

3. Pseudonymise or anonymise for non-production use

Never copy production personal data into development or test databases without masking it:

1
2

UPDATE customers SET name = CONCAT('Customer ', id), email = CONCAT('user', id, '@example.invalid');
SELECT id, name, email FROM customers ORDER BY id;

1
2
3
4
5
6

+----+------------+-----------------------+
| id | name       | email                 |
+----+------------+-----------------------+
|  1 | Customer 1 | user1@example.invalid |
+----+------------+-----------------------+
1 row in set

(.invalid and example.com domains never receive real mail.)

4. Delete when asked, and when time is up

• Provide a way to erase or anonymise a person’s data on request. A soft delete (Soft Delete) does not satisfy an erasure request.
• Define a retention period for each kind of data, and run a purge (Events, Changing Millions of Rows).
• Remember backups, logs and replicas: personal data lives there too. Backups should expire on a schedule.

5. Encrypt

TLS for connections, encryption at rest for storage, application-level encryption for the most sensitive fields (Encrypted Connections).

6. Keep a record

Know where personal data is stored (tables and columns), who has access, and why it is processed. A simple data inventory, even a spreadsheet, helps with audits and requests.

An access review routine

1

SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema') ORDER BY grantee, table_name;

1

Empty set

Review who has access to tables with personal data, every quarter, and remove access that is no longer needed.