Database Management · Beginner level

Beginner level · Module 5: Database Care: Indexes, Backups and Access · Lesson 3 of 3

Access Control and Personal Data

Give each person only the access they need and handle personal data responsibly.

In this lesson you will

  • Apply least privilege with users and roles
  • Identify personal data and basic protection measures

Key concepts

least privilegeusers and rolesGRANT and REVOKEpersonal data

The lesson

Least privilege means each person or application gets only the access it needs. In PostgreSQL, create roles such as bursar (read and add payments) and head teacher (read only) and grant permissions with GRANT SELECT, INSERT ON payments TO bursar;.

SQLite has no users; protect the file instead with operating-system permissions, encryption of the device and careful sharing.

Student, patient and beneficiary records are personal data. Collect only what you need, restrict who can see it, keep it no longer than necessary and never share copies by email or messaging apps without protection. Follow your organisation's policy and the laws that apply to it.

Step by step

  1. List who uses the school fees data and what they need.
  2. Create roles with suitable permissions in PostgreSQL.
  3. Test that a read-only role cannot change data.
  4. Write a one-page access and privacy note.

Practice activity

Write the access policy for the school fees database and implement it with roles.

Expected result: Each role can do only what the policy allows, demonstrated by test queries.

Check your understanding

  1. What is the principle of least privilege?

    Show answer

    Give each user only the access they need.

  2. Name two ways to protect personal data in a database.

    Show answer

    For example restricting access by role and collecting only the data that is needed.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.