Database Management · Advanced level

Advanced level · Module 6: Database Capstone · Lesson 2 of 5

Implementing Schema and Security

Build the database with roles, row-level security, encryption and auditing.

In this lesson you will

  • Implement the schema and access model
  • Prove the security controls with tests

Key concepts

schema scriptsroles and RLSTLS and auditingsecurity tests

The lesson

Build everything from versioned scripts: schema, roles, policies, audit setup and sample data with invented records.

Write security tests: each role attempts allowed and forbidden operations, including cross-clinic access, and the expected results are checked automatically.

Require TLS for remote connections and confirm auditing records changes to sensitive tables.

Step by step

  1. Write and run the schema and role scripts.
  2. Implement row-level security and auditing.
  3. Write automated security tests.
  4. Verify encrypted connections.

Practice activity

Complete milestone 2: the secured database with passing security tests.

Expected result: All security tests pass and the database can be rebuilt from scripts.

Check your understanding

  1. Why automate security tests?

    Show answer

    So access rules are checked every time the database changes.

  2. Where should sample data come from?

    Show answer

    It should be invented, never real patient data.

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.