All subjects · Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development

Controlling access and views with a DBMS

Edexcel IAL Information Technology · WIT13 2 hour written exam on data, enabling technologies and systems development

A database management system (DBMS) is the software that stores, retrieves and protects a database. Its security features control who can see and change which data, and views let each user see only what they need (spec 13.4.3).

Practise this in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise this lesson

What a DBMS does

A DBMS sits between users or applications and the stored data. Functions: define the structure (tables, fields, keys, relationships, data dictionary); store, retrieve and update data with a query language such as SQL; enforce validation and referential integrity; manage concurrent access with locking and transactions; backup and recovery; and security: authentication, access rights, views, encryption and audit logs.

Access control

Authentication checks who the user is (username and password, multi-factor). Authorisation decides what they may do.

Access control models:

Privileges (SELECT, INSERT, UPDATE, DELETE on each table) are granted and revoked by the database administrator. The principle of least privilege: give each user only the access needed for their job.

Views

A view is a virtual table defined by a stored query: it shows selected rows and columns, possibly from several joined tables, without storing a separate copy.

Uses:

Types: simple views (one table, can usually be updated), complex views (joins, aggregates; often read-only), read-only and updatable views.

Worked example

A school database holds STUDENT(StudentID, Name, Address, MedicalNotes, Grades). Design access for teachers, the school nurse and the office.

  1. Use role-based access with three roles.
  2. Teachers: a view showing StudentID, Name, Grades; UPDATE allowed on Grades only.
  3. Nurse: a view showing StudentID, Name, MedicalNotes; read and update MedicalNotes.
  4. Office: a view showing Name and Address; no access to grades or medical notes.
  5. Audit log records every change; least privilege throughout.

Answer: Each role sees only the data it needs through its own view

Key idea: DBMS functions include security. Authenticate, then authorise: discretionary, mandatory, role-based, rule-based; grant and revoke privileges; least privilege. Views are virtual tables showing only selected data.

Check yourself

  1. What is a database view?
    • A printed report
    • A virtual table produced by a stored query that shows selected data
    • A backup copy of a table
    • A screen resolution setting
    Show answer

    A virtual table produced by a stored query that shows selected data. Views do not store a separate copy of the data.

  2. A hospital gives permissions to 'Nurse', 'Doctor' and 'Receptionist' and assigns each employee to one of these. Which model is this?
    • Discretionary access control
    • Mandatory access control
    • Rule-based access control
    • Role-based access control
    Show answer

    Role-based access control. Permissions attach to roles.

  3. Access to the payroll table is only allowed from 08:00 to 18:00 on weekdays. Which model is this?
    • Role-based access control
    • Rule-based access control
    • Discretionary access control
    • A complex view
    Show answer

    Rule-based access control. Conditions such as time define access.

  4. What does the principle of least privilege mean?
    • All users get administrator rights
    • Only one person may use the database
    • Users get only the access they need to do their job
    • Passwords should be as short as possible
    Show answer

    Users get only the access they need to do their job. Minimises damage from mistakes and misuse.

  5. A researcher may see the average age of patients per ward but not individual records. What should the DBMS provide?
    • An aggregate view granted to the researcher
    • Full access to the PATIENT table
    • A copy of the database on a USB stick
    • Administrator rights
    Show answer

    An aggregate view granted to the researcher. Aggregates hide individuals.

  6. An employee moves from the finance team to marketing. What should the database administrator do?
    • Leave all existing privileges in place
    • Delete the employee's records
    • Revoke the finance privileges and grant the marketing ones
    • Give the employee administrator rights
    Show answer

    Revoke the finance privileges and grant the marketing ones. Access should match the current role.

  7. Explain why a DBMS keeps an audit log of changes.
    Show answer

    It records who changed which data and when, so unauthorised or mistaken changes can be traced and investigated, which also deters misuse.. Who, what, when, so traceable.

Practise with instant feedback

Sources

Last updated 8 November 2026. Spotted a mistake? Tell us.