Database Securitypracticeadvanced
DCL Practice
Hands-on practice: configuring roles, granting privileges, and testing access control.
Overview
Students practise setting up a multi-role permission model. Tasks simulate common real-world scenarios: a read-only reporting user, a write-access application user, and an admin role. Each student sets up their own environment and tests the permissions.
Lesson Plan
| Time | Activity |
|---|---|
| 0–10 min | Recap: DCL commands and role hierarchy |
| 10–20 min | Walk through first task together |
| 20–60 min | Hands-on work: configure roles and test access |
| 60–75 min | Review: connect as each role and verify permissions |
| 75–80 min | Wrap-up |
Key Concepts
- Testing permissions by connecting with the target role
SET ROLE role_nameto switch roles within a session\dp(psql) or information_schema views to inspect current grants- Schema-level USAGE privilege required before object-level grants
- Revoking from PUBLIC to remove default privileges
✏️Practice
Tasks will be provided during the lesson.