Database Administration

IntermediateDatabases
MySQLPostgreSQLpg_dump

Objective

Perform DBA tasks: user management, permissions, and monitoring.

Tools & Technologies

  • MySQL
  • PostgreSQL
  • pg_dump

Key Commands

CREATE USER 'app'@'%' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT ON db.* TO 'app'@'%';
SHOW PROCESSLIST;
SELECT * FROM pg_stat_activity;

Lab Steps

01
User Creation

Create database users with limited privileges.

02
GRANT/REVOKE

Apply principle of least privilege with GRANT and REVOKE.

03
Query Monitoring

Identify long-running queries with PROCESSLIST and pg_stat_activity.

04
Kill Queries

Terminate blocking or runaway queries safely.

Challenges Encountered

  • GRANT ALL on *.* is overly permissive — never for production
  • Killing active transactions can cause rollbacks

Key Takeaways

  • Create separate users per application — never share credentials
  • Audit database logins and privilege escalations