- ✓Database user management involves creating accounts, assigning roles, and applying the principle of least privilege to minimise security risk.
- ✓A comprehensive backup strategy includes regular full backups, incremental backups, and offsite or cloud storage to protect against data loss.
- ✓Recovery objectives, the Recovery Time Objective and Recovery Point Objective, define how quickly and how completely data must be restored after an incident.
- ✓Database security hardening involves removing default accounts, encrypting sensitive data, auditing access logs, and applying security patches promptly.
- ✓Performance monitoring tools built into DBMS platforms, such as MySQL's Performance Schema and PostgreSQL's pg_stat_statements, provide insight into query behaviour.
Listen to the full episode inside the course. Enrol to access all 80 episodes, plus assignments, tutor support and Student Finance funding.
Start learning →Alex: Today we're looking at database administration. Sam, what is a database administrator responsible for?
Sam: The DBA's responsibilities cover the full lifecycle of a database system in production. They ensure the database is available when users need it, performs at an acceptable level, is protected from data loss, is secured against unauthorised access, and evolves in a controlled way as the schema and data volumes change. It's a role that combines technical depth with operational discipline.
Alex: Let's start with user management. How do DBAs control who can do what?
Sam: Database user management follows the principle of least privilege: every user account has only the permissions it needs for its specific purpose and nothing more. Application accounts that run SELECT queries don't need INSERT or DELETE permissions. A reporting user that only needs to read data shouldn't be able to modify it. DBAs create and manage roles, which bundle related permissions together, and assign users to roles. Regular audits of user accounts ensure that former employees' accounts are disabled and that permissions haven't accumulated inappropriately over time.
Alex: And backup strategies?
Sam: A comprehensive backup strategy is the foundation of data resilience. Full backups capture the entire database at a point in time. Incremental backups capture only the changes since the last backup, reducing backup time and storage. Transaction log backups, in databases that support them, allow point-in-time recovery: restoring to any point in time, not just the time of the last full backup. The backup frequency and retention period are determined by the organisation's recovery objectives: how much data can be lost, and how quickly does the database need to be restored.
Alex: What's the difference between RTO and RPO?
Sam: RTO, Recovery Time Objective, is the maximum acceptable time for restoring a service after a failure. RPO, Recovery Point Objective, is the maximum acceptable amount of data loss measured in time. An RPO of one hour means you're prepared to lose at most the last hour of data. These objectives drive the design of the backup and recovery architecture: a very low RPO requires very frequent backups or continuous replication to a standby database.
Alex: How do you test backup and recovery?
Sam: Regularly and rigorously. A backup you've never tested is a backup you can't rely on. DBAs should conduct scheduled recovery tests where they actually restore the database from backup and verify that the restored data is complete and correct. This should happen at least quarterly and after any significant change to the backup infrastructure. Document each test and its results, because the ability to recover is a critical control that auditors and management need evidence of.
Alex: What about security hardening?
Sam: Database security hardening involves removing unnecessary default accounts and permissions, disabling features that aren't being used, configuring encryption for data in transit and at rest, setting up auditing to log all privileged access and data modifications, and applying patches promptly. Database security configuration standards, like the CIS Benchmarks for specific database platforms, provide detailed checklists of recommended settings.
Alex: Brilliant. Thanks Sam. In the final Unit 13 lesson we look at query optimisation.