Database Administration

Objective

Perform DBA tasks: create users with least-privilege access, monitor queries, and kill long-running sessions.

Tools & Technologies

users
GRANT
REVOKE
SHOW PROCESSLIST
pg_stat_activity
EXPLAIN

Key Commands

CREATE USER 'app'@'%' IDENTIFIED BY 'pass';

GRANT SELECT,INSERT ON db.* TO 'app'@'%';

SHOW PROCESSLIST;

SELECT * FROM pg_stat_activity;

KILL QUERY 42;

Architecture Overview

sequenceDiagram participant DBA as DBA participant DB as Database participant APP as Application User DBA->>DB: CREATE USER app@'%' DBA->>DB: GRANT SELECT,INSERT ON mydb.* APP->>DB: Connect as 'app' DB->>APP: Access granted (limited) APP->>DB: SELECT * FROM users DB->>APP: Results APP->>DB: DROP TABLE DB->>APP: Permission denied

Step-by-Step Process

User Management

Create users with minimal necessary privileges.

-- MySQL
CREATE USER 'webapp'@'10.0.0.%' IDENTIFIED BY 'strongpassword';
GRANT SELECT, INSERT, UPDATE ON appdb.* TO 'webapp'@'10.0.0.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'webapp'@'10.0.0.%';

-- PostgreSQL
CREATE USER webapp WITH PASSWORD 'strongpassword';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO webapp;

Monitor and Kill Queries

Find and terminate long-running or blocking queries.

-- MySQL
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
KILL QUERY 42;    -- kill query, keep connection
KILL 42;          -- kill connection

-- PostgreSQL
SELECT pid, now()-query_start AS duration, query
FROM pg_stat_activity
WHERE state='active' ORDER BY duration DESC;

SELECT pg_cancel_backend(pid);  -- cancel query
SELECT pg_terminate_backend(pid); -- terminate connection

Challenges & Solutions

GRANT ALL PRIVILEGES is almost never appropriate for application users
Killing a connection mid-transaction causes rollback

Key Takeaways

Principle of least privilege: grant only the specific operations the app needs
Create separate users per application — never share database credentials