Database security: authorisation, encryption, backup and auditing
You will build a shared security framework for MySQL, PostgreSQL, MongoDB and Redis, protecting data with authorisation, encryption, backups and auditing.
Level 401 · 18 lessons · 15 practice labs
Lessons
Threat model and access
- Database threat model and data inventory: You will list which data sits in which database and rank the biggest risks in an inventory.
- Auditing your own database ports with Nmap: You will check with Nmap on your own servers whether ports 3306, 5432, 27017 and 6379 are exposed, and close them.
- Authentication and secrets management: You will keep passwords out of code, store them in environment variables or a secrets vault and rotate them regularly.
- Authorisation: least privilege, separation of duties and role design: You will separate application, reporting and admin accounts and apply least privilege across every engine through role design.
- Version and patch management
- Short task: an exposure and access review of four engines
Encryption and data protection
- Encryption in transit: TLS across four engines: You will learn certificates, CAs and TLS versions and make connections encrypted and verified on all four databases.
- Encryption at rest and key management: You will grasp what disk and database encryption each protect and why keys must be kept apart from the data.
- Data masking, pseudonymisation and tokenisation: You will mask fields such as card or ID numbers in test environments and reports so real data stays hidden.
- Data classification, retention and deletion: You will classify data by sensitivity and apply retention and deletion rules required by regulations such as GDPR and PCI DSS.
- Keeping real data out of test environments
- Short task: classify and encrypt sensitive columns
Backup, auditing and monitoring
- Backup security: the 3-2-1 rule, encrypted and immutable copies: You will encrypt backups and keep them off-site and undeletable so you can recover even after ransomware.
- Restore drills and recovery targets (RPO/RTO): You will prove backups actually restore and set how much data and time you can afford to lose (RPO and RTO).
- Audit log design: what, who and when: You will decide which events to log (logins, privilege changes, bulk reads) and collect the log somewhere tamper-resistant.
- Monitoring unusual queries and alert rules: You will write alert rules that catch patterns such as bulk reads at midnight or repeated failed logins.
- Layered defence against injection: the database side: You will limit the impact of injection with low-privilege accounts, views and hidden error messages on top of parameterised queries.
- Capstone: audit and harden a multi-engine environment: You will audit an environment with MySQL, PostgreSQL, MongoDB and Redis against a checklist and close findings by priority.
Practice labs
- Scan for exposed database ports: Use Nmap on your own lab network to find which database ports are exposed and their versions.
- Build a threat model from a data inventory: Map where sensitive data sits in a health clinic's databases and write down the three most likely threats.
- Check patch levels across four engines: Compare four engines' versions with known vulnerabilities and prepare a prioritised patch plan.
- Narrow an application account's privileges: Restrict an all-powerful app account to reading and writing only the tables it needs.
- Move an embedded password into a secret store: Move a database password from source code into an environment variable or vault and rotate the old one.
- Design a role model with separation of duties: Build roles where developers, operators and auditors cannot do each other's work and verify with cross tests.
- Check whether a connection is encrypted: Verify from the server side and with a packet capture whether a client connection uses TLS.
- Encrypt a sensitive column with an app key: Keep the ID number column encrypted, store the key away from the database and show authorised reads.
- Rotate an encryption key by re-encrypting: Move data encrypted with a key suspected of leaking to a new key without downtime and retire the old one.
- Classify sensitive columns: Prepare a table that labels an HR database's columns as personal, sensitive or public.
- Produce a masked copy for testing: Make a test copy from production data that masks names, phones and e-mails but keeps relationships intact.
- Safely delete data past its retention period: Build a job that purges records past the retention policy, including in backups, and leaves evidence.
- Find who deleted what in the audit log: From audit records, find who deleted a customer record, when and from which address.
- Alert on unusual bulk reads: Write a rule that fires when an account reads far more rows than usual and test it with a simulated leak.
- Add database-side layers against injection: In a vulnerable app, limit and prove the impact of injection using least privilege, stored procedures and query monitoring.
About this course
You will build a shared security framework for MySQL, PostgreSQL, MongoDB and Redis, protecting data with authorisation, encryption, backups and auditing.
What you will learn
- Threat model and access
- Encryption and data protection
- Backup, auditing and monitoring
Prerequisites
You should have practised the topics of the same path up to level 301.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 401; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.