Database Security & Privacy in MySQL — A Step-by-Step Guide

By Rishika Raj || Subject: Database Security & Privacy

This project demonstrates practical MySQL operations using both DDL and DML commands. Tasks include creating databases and tables, inserting and updating records, managing users with restricted access, and applying basic data security methods such as encryption, views, and record deletion for privacy.

Tools used: MySQL 8 with MySQL Workbench as the SQL editor (or the MySQL command-line client).

1. Setup & Connect

Open your MySQL client and log in as root:

mysql -u root -p

Screenshot: the mysql> prompt after login.

2. Create Database & Customers Table (DDL/DML)

Run the SQL below to create the securedb database and the customers table, insert sample rows, and view them.

CREATE DATABASE securedb;
USE securedb;

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100),
  email VARCHAR(100),
  phone VARCHAR(15)
);

INSERT INTO customers (full_name, email, phone)
VALUES ('rishika', 'rishika@gmail.com', '9876543210'),
('shreyas', 'shreyas@yahoo.com', '9123456780');

SELECT * FROM customers;

Screenshot: output of SELECT * FROM customers; (shows rows you inserted).

3. Access Control: Create a Read-Only User

Principle: give users only the minimum privileges required (least privilege).

CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'Read123';
GRANT SELECT ON securedb.customers TO 'readonly'@'localhost';
SHOW GRANTS FOR 'readonly'@'localhost';

Screenshot: result of SHOW GRANTS FOR 'readonly'@'localhost';

4. Encrypt Sensitive Data (SSN)

Use MySQL's AES functions to store SSNs encrypted at rest. Only trusted users with the key can decrypt.

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  ssn VARBINARY(255)
);

INSERT INTO employees (name, ssn)
VALUES ('rishika raj', AES_ENCRYPT('123-45-6789', 'MyKey123'));

-- View encrypted (binary)
SELECT * FROM employees;

-- Decrypt (admin only)
SELECT name,
       CAST(AES_DECRYPT(ssn, 'MyKey123') AS CHAR) AS ssn_plain
FROM employees;

Screenshots: (1) encrypted binary column view, (2) decrypted plaintext (admin only).

2.

5. Data Masking (View)

Provide only partial data to untrusted users by using a masked view. The view reads the encrypted column (server-side) and returns masked output.

CREATE OR REPLACE VIEW employees_masked AS
SELECT id, name,
  CONCAT('XXX-XX-', RIGHT(CAST(AES_DECRYPT(ssn, 'MyKey123') AS CHAR), 4)) AS masked_ssn
FROM employees;

GRANT SELECT ON securedb.employees_masked TO 'readonly'@'localhost';

SELECT * FROM employees_masked;

Screenshot: masked view showing XXX-XX-6789 for SSN.

6. Delete Data (Right to be Forgotten)

To remove a user’s record permanently (privacy compliance), delete by primary key. Avoid deleting by non-indexed fields when safe-update mode is enabled .

-- Show IDs
SELECT id, name FROM employees;

-- Delete safely by ID
DELETE FROM employees WHERE id = 1;

-- Verify deletion
SELECT * FROM employees;

Screenshot: verification showing the row removed.

Extra Security Tips

• Never store encryption keys in plain text in application code — use a KMS in production.
• Use SSL/TLS for MySQL connections: set require_secure_transport=ON in my.cnf.
• Regularly rotate passwords and keys, and restrict backup access (encrypt backups).
• Use audit logging (audit plugin or triggers) to track sensitive operations.

Conclusion

Database security is not just about storing information—it’s about ensuring that the right people have the right access at the right time, while sensitive data stays protected. In this project, we explored how MySQL supports privacy and security through core practices like access control, encryption, data masking, and deletion. These techniques demonstrate that even simple SQL commands, when applied thoughtfully, can make a database far more resilient to misuse or leaks. By combining these measures, organizations and developers alike can build systems that are both functional and trustworthy.