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).
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:
- Discretionary: the owner of data decides who can access it.
- Mandatory: access is set centrally by security levels (for example confidential, secret) that users cannot change.
- Role-based: permissions are given to roles (receptionist, nurse, manager) and users are assigned roles; easy to manage when staff change jobs.
- Rule-based: access depends on rules, such as only during working hours or only from the office network.
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:
- Security: a receptionist's view of PATIENT shows name and appointment time but hides diagnosis; users are granted access to the view, not the base table.
- Simplicity: complex joins are hidden behind a simple view.
- Privacy: aggregate views (totals, averages) give statistics without revealing individuals.
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.
- Use role-based access with three roles.
- Teachers: a view showing StudentID, Name, Grades; UPDATE allowed on Grades only.
- Nurse: a view showing StudentID, Name, MedicalNotes; read and update MedicalNotes.
- Office: a view showing Name and Address; no access to grades or medical notes.
- Audit log records every change; least privilege throughout.
Answer: Each role sees only the data it needs through its own view
Check yourself
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
Sources
- Pearson Edexcel International Advanced Level in Information Technology specification (first teaching 2018), Issue 2, February 2021
- Pearson Edexcel IAL Information Technology: scheme of work for Unit 3
Last updated 8 November 2026. Spotted a mistake? Tell us.