Database Security and User Management

Universities host sensitive information: grades, contact details, and instructor assignments. Protecting that data requires thoughtful user management and permission design. In this lesson we map security concepts directly to the University schema.

Learning Goals

• Create database accounts aligned with campus roles
• Grant the minimum privileges necessary
• Build roles (groups of privileges) for registrars, instructors, and analysts
• Introduce row-level security patterns for protecting grade data

Users and Roles

We will reference PostgreSQL syntax, but similar commands exist in other engines.

Create application accounts

CREATE USER registrar_app  WITH PASSWORD 'change_me_registrar';
CREATE USER instructor_app WITH PASSWORD 'change_me_instructor';
CREATE USER reporting_app  WITH PASSWORD 'change_me_reporting';

Next, define roles that collect privileges:

Define least-privilege roles

CREATE ROLE role_registrar;
CREATE ROLE role_instructor;
CREATE ROLE role_analytics;

-- Give registrars full control over enrollment
GRANT SELECT, INSERT, UPDATE, DELETE ON enrollment TO role_registrar;
GRANT SELECT ON student, course, department, instructor TO role_registrar;

-- Instructors may only read their courses and update grades
GRANT SELECT ON course, student, department TO role_instructor;
GRANT SELECT, UPDATE (grade) ON enrollment TO role_instructor;

-- Analysts receive read-only access
GRANT SELECT ON student, course, department, instructor, enrollment TO role_analytics;

-- Attach users to roles
GRANT role_registrar  TO registrar_app;
GRANT role_instructor TO instructor_app;
GRANT role_analytics  TO reporting_app;

By default these users still cannot create tables or manage other objects because no global privileges were granted.

Schema and Object Ownership

Keep application tables in a dedicated schema to isolate permissions.

CREATE SCHEMA university AUTHORIZATION registrar_app;
ALTER ROLE role_registrar  SET search_path = university, public;
ALTER ROLE role_instructor SET search_path = university, public;
ALTER ROLE role_analytics SET search_path  = university, public;

Ensure future tables inherit read access for reporting users:

ALTER DEFAULT PRIVILEGES IN SCHEMA university
GRANT SELECT ON TABLES TO role_analytics;

Row-Level Security (RLS)

For finer control—such as limiting instructors to their own courses—enable row-level security.

Enable RLS on enrollment

ALTER TABLE enrollment ENABLE ROW LEVEL SECURITY;

CREATE POLICY instructor_can_view_own_course
ON enrollment
FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM course
    WHERE course.course_id = enrollment.course_id
      AND course.instructor_id = current_setting('app.current_instructor')::INTEGER
  )
);

CREATE POLICY instructor_can_update_grade
ON enrollment
FOR UPDATE
USING (
  EXISTS (
    SELECT 1
    FROM course
    WHERE course.course_id = enrollment.course_id
      AND course.instructor_id = current_setting('app.current_instructor')::INTEGER
  )
)
WITH CHECK (true);

Application code sets the app.current_instructor parameter after authenticating the faculty member:

SELECT set_config('app.current_instructor', '4', false);

Registrars can bypass RLS by granting them ALTER TABLE enrollment DISABLE ROW LEVEL SECURITY; or by having them connect with a role that owns the table.

Auditing and Password Hygiene

• Force periodic password changes:

  ALTER ROLE instructor_app VALID UNTIL '2025-12-31';

• Require SSL/TLS connections: configure your database server to enforce secure channels for remote clients.

• Log privileged operations. PostgreSQL's pgaudit or a custom audit_log table can capture changes to critical tables.

• Remove unused accounts quickly (DROP USER ...) and lock accounts during investigations:

  ALTER ROLE instructor_app NOLOGIN;

Summary

• Create separate users for each application or integration point.
• Grant roles aligned to job duties: registrar (full write), instructor (limited update), analytics (read-only).
• Use row-level security or views to protect sensitive slices of data like grades.
• Review privileges regularly and enforce password policies and secure connections.

Quiz

Show quiz

1. Which privilege lets instructors change grades without editing other columns?
   A) GRANT UPDATE ON enrollment TO role_instructor;
   B) GRANT UPDATE (grade) ON enrollment TO role_instructor;
   C) GRANT ALL ON enrollment TO role_instructor;
   D) GRANT SELECT ON enrollment TO role_instructor;

2. Why create roles such as role_registrar instead of granting privileges directly to users?
   A) Roles are faster
   B) Roles allow you to manage permissions once and assign them to many users
   C) Users cannot hold direct privileges
   D) Roles automatically enable RLS

3. How can the application indicate which instructor is connected when using RLS policies above?
   A) Change their password
   B) Call set_config('app.current_instructor', 'ID', false);
   C) Set the database timezone
   D) Grant them superuser

4. True or False: Granting SELECT on the university schema automatically gives access to future tables.
   A) True
   B) False

---

Answers

1. B
2. B
3. B
4. B