Hello dear students! Welcome to Chapter 6 — Database Security and Authorization. I know security topics can feel a bit dry at first, but trust me, this chapter is full of practical concepts that you will see in your exam and also in real-world database jobs. Let me walk you through everything step by step. Ready? Let’s begin!
1. Introduction to Database Security Issues
Before we dive into the technical details, let me ask you a simple question: Why do we lock our doors at night? Because we want to protect what is inside from people who should not be there. Database security works on the exact same principle.
1.1 Security vs Authorization
Security means protecting data or information from unauthorized users. It is about keeping the wrong people out. Authorization means allowing a user to have the right to utilize a resource (the database). Security is the fence; authorization is the key you give to specific people.
1.2 Threats to Databases
Threats to databases can result in the loss or degradation of three commonly accepted security goals:
Let me give you a real example for each. Loss of integrity: A hacker changes a student’s grade from B to A in the database. Loss of availability: The server crashes and nobody can access the student portal for three hours during registration. Loss of confidentiality: A network failure exposes all patient medical records to the public internet.
1.3 Four Countermeasures
To protect against these threats, we implement four kinds of countermeasures:
- Access control — Restricting who can log in and what they can do
- Inference control — Controlling access to statistical databases so users cannot deduce individual values
- Flow control — Preventing information from flowing to unauthorized users through hidden channels
- Data encryption — Encoding sensitive data so unauthorized users cannot read it even if they access it
A DBMS includes a database security and authorization subsystem responsible for all of this. There are two main types of security mechanisms:
- Discretionary security mechanisms — Based on granting and revoking privileges (we control who gets what)
- Mandatory security mechanisms — Based on system-enforced security levels (the system decides based on classification)
Q1 (MCQ). A hacker modifies the salary values in the EMPLOYEE table without permission. Which security goal has been violated?
a) Availability
b) Confidentiality
c) Integrity
d) Accountability
Q2 (MCQ). Which countermeasure is specifically designed to prevent users from deducing individual values from statistical query results?
a) Access control
b) Inference control
c) Flow control
d) Data encryption
Q3 (Fill in the blank). The two main types of database security mechanisms are ________ security mechanisms and ________ security mechanisms.
A1. c) Integrity. Integrity means data is correct and proper. Improper modification (changing salary values without permission) directly violates integrity. Availability would be about data being inaccessible, and confidentiality would be about data being exposed — neither applies here.
A2. b) Inference control. Inference control is specifically about statistical databases. Even when users are only allowed to see aggregate results (averages, counts, etc.), they might combine multiple queries to figure out individual values. Inference control prevents this.
A3. Discretionary; Mandatory
2. Access Control, Flow Control, and Encryption in Detail
2.1 Access Control
Access control is the most basic form of protection. It is handled by creating user accounts and passwords to control the login process. If you don’t have a valid account, you simply cannot enter the database system at all. Think of it as the front gate of a building.
2.2 Flow Control
Flow control goes deeper. It prevents information from reaching unauthorized users through any pathway — not just direct access. A flow policy specifies the channels along which information is allowed to move.
The simplest flow policy uses two classes:
Covert channels are hidden pathways that allow information to flow implicitly, violating the security policy. For example, a malicious program might use timing differences (responding faster or slower) to leak confidential information to an unauthorized user without directly sending the data.
2.3 Data Encryption
Data encryption protects sensitive data (like credit card numbers) being transmitted through a network. The data is encoded using an algorithm. Even if an unauthorized user intercepts it, they cannot read it without the decryption key.
Worked Example from your textbook:
Plain text: “Hi Alex this is my Pin number 5656”
Let the mapping be: H=O, i=$, A=B, l=M, e=K, x=l, 5=a, 6=8
Cipher text: O$ BMKl th$s $s My P$n number a8a8
See how the original message is completely hidden? Someone who sees “O$ BMKl th$s $s My P$n number a8a8” has no idea what it means unless they have the mapping (the key).
Q1 (True/False). In a simple two-class flow policy, information is allowed to flow from Non-confidential to Confidential.
Q2 (MCQ). A malicious program leaks sensitive data by varying its CPU usage patterns. This is an example of:
a) Access control violation
b) A covert channel
c) Data encryption failure
d) Inference control problem
Q3. Explain the difference between encryption and access control as countermeasures.
A1. True. In the two-class flow policy (Confidential and Non-confidential), the ONLY blocked flow is from C to N. All other flows are allowed, including N → C. This makes sense because giving confidential information to someone who already has higher clearance does not violate security.
A2. b) A covert channel. A covert channel is a hidden pathway that allows information to flow implicitly in a way that violates the security policy. CPU usage patterns, timing differences, and file locking behavior are classic examples of covert channels — the data itself is not directly sent, but information is encoded in observable side effects.
A3. Access control prevents unauthorized users from entering the database system at all — it acts as a gatekeeper. If you don’t have an account and password, you cannot access anything. Encryption assumes the user might gain access to the data (for example, by intercepting network traffic), but makes the data unreadable by encoding it. Access control stops the person at the door; encryption scrambles the contents so even if they get in, they can’t understand what they see.
3. The Role of the DBA in Security
The Database Administrator (DBA) is the central authority for managing the entire database system. When it comes to security, the DBA carries enormous responsibility.
The DBA has a special account — sometimes called a system account or super user account. This account provides powerful capabilities:
Notice the mapping? Account creation is access control. Granting and revoking privileges are discretionary security mechanisms. Security level assignment is for mandatory security. The DBA handles all of these.
The DBA also classifies users and data according to the organization’s security policy. Not every user should see every piece of data, and the DBA decides who sees what.
3.1 User Accounts and Database Audit
When someone needs to access the database, they must apply for a user account. The DBA creates an account ID and password if there is a legitimate need. Every time the user wants access, they must log in with these credentials.
But logging in is not the end of tracking. The database system keeps a record of everything that happens:
- System log: Records all updates applied to the database and who applied each update. Used for recovery from failures.
- Database audit: The process of reviewing the log to examine all accesses and operations during a certain time period.
- Audit trail: A database log used specifically for security purposes — tracking who did what and when.
System log = for recovery purposes (what changed, who changed it)
Audit trail = for security purposes (tracking all access for investigation)
Database audit = the act of reviewing the audit trail
Q1 (MCQ). Which DBA capability belongs to mandatory security rather than discretionary security?
a) Account creation
b) Privilege granting
c) Privilege revocation
d) Security level assignment
Q2 (Fill in the blank). A ________ is a database log that is used mainly for security purposes to track all accesses and operations applied by users.
Q3. Differentiate between a system log and an audit trail. Can they be the same physical log?
A1. d) Security level assignment. Security level assignment (classifying data and users into levels like Top Secret, Secret, etc.) is the mechanism for mandatory access control. The other three — account creation (access control), privilege granting, and privilege revocation — are discretionary mechanisms where the DBA decides who gets what privileges.
A2. Audit trail. An audit trail is specifically a database log used for security purposes. It records all operations applied to the database along with the user who applied them, enabling security officers to investigate suspicious activity.
A3. A system log primarily serves recovery purposes — it records all updates so the system can undo or redo operations after a crash. An audit trail primarily serves security purposes — it tracks who accessed what for investigation. In practice, they can be the same physical log because both need to record operations and the user who performed them. The difference is in how the log is used, not in its physical storage.
4. Discretionary Access Control (DAC) — Deep Dive
Now we arrive at one of the most important and exam-heavy topics in this chapter. Discretionary Access Control (DAC) is the typical method of enforcing access control in a database system. It is based on granting and revoking privileges.
Why is it called “discretionary”? Because the owner of the data has the discretion (the choice) to decide who gets which privileges. The owner can give privileges to anyone they want and take them back whenever they want.
4.1 Two Levels of Discretionary Privileges
Level 1: Account Level
At this level, the DBA specifies privileges for each account independently of any specific relations. These are general capabilities given to the account itself.
Syntax: GRANT <privilege> TO <user>; Example: GRANT CREATETAB TO STAFF;
Level 2: Relation Level (Table Level)
At this level, the DBA (or relation owner) controls access to each individual relation — both base tables and views.
Syntax: GRANT <operation> ON <table> TO <user>; Example: GRANT INSERT, DELETE ON Stud-detail TO STAFF;
4.2 The Access Matrix Model
DAC follows an authorization model called the access matrix model. This is a conceptual tool that helps us visualize who can do what.
The rows represent subjects (users/accounts). The columns represent objects (relations, views). Each cell M(i,j) shows what privileges user i has on relation j.
4.3 Relation-Level Privileges in Detail
When the owner of a relation grants privileges on that relation, there are three main types:
1. SELECT privilege on R: Gives the account the right to use the SELECT statement to retrieve tuples from R. This is a read-only privilege.
2. MODIFY privileges on R: This is further divided into three sub-privileges:
- UPDATE — Modify existing tuples
- DELETE — Remove tuples
- INSERT — Add new tuples
Important: Both INSERT and UPDATE can be restricted to specific attributes. For example, you can allow a user to UPDATE only the SALARY column, not all columns.
3. REFERENCES privilege on R: This gives the account the capability to reference relation R when specifying integrity constraints (like foreign keys). This can also be restricted to specific attributes.
4.4 Specifying Privileges Using Views
This is a very powerful and exam-frequent technique. Suppose the owner A of relation R wants another user B to access only some fields or only some tuples of R. A cannot do this with a simple GRANT on R. Instead, A uses views.
Example 1 — Restricting columns: A wants B to see only NAME and ADDRESS from EMPLOYEE.
— Step 1: A creates a view with only the allowed columns CREATE VIEW EmpPartial AS SELECT NAME, ADDRESS FROM EMPLOYEE;— Step 2: A grants SELECT on the VIEW (not the original table) GRANT SELECT ON EmpPartial TO B;
Example 2 — Restricting rows: A wants B to see only employees in department 5.
— Step 1: A creates a view with a WHERE condition CREATE VIEW EmpDept5 AS SELECT * FROM EMPLOYEE WHERE DNO = 5;— Step 2: A grants SELECT on the view GRANT SELECT ON EmpDept5 TO B;
B can now SELECT from EmpPartial or EmpDept5, but B cannot directly access the EMPLOYEE table. The view acts as a controlled window into the data.
Q1 (MCQ). Owner A wants to allow user B to update only the SALARY column of the EMPLOYEE table. Which SQL command is correct?
a) GRANT UPDATE ON EMPLOYEE TO B;
b) GRANT MODIFY ON EMPLOYEE (SALARY) TO B;
c) GRANT UPDATE ON EMPLOYEE (SALARY) TO B;
d) GRANT UPDATE SALARY ON EMPLOYEE TO B;
Q2 (MCQ). In the access matrix model, what do the rows and columns represent?
a) Rows = objects, Columns = privileges
b) Rows = subjects (users), Columns = objects (relations)
c) Rows = privileges, Columns = subjects (users)
d) Rows = security levels, Columns = privileges
Q3. User A owns the EMPLOYEE table and wants user B to only see employees who work in department 3, and only their NAME and SALARY. Write the SQL statements A must execute.
A1. c) GRANT UPDATE ON EMPLOYEE (SALARY) TO B; The correct SQL syntax for restricting UPDATE to specific columns is: GRANT UPDATE ON table_name (column_list) TO user. Option a would allow updating ALL columns. Option b uses “MODIFY” which is an account-level privilege, not a relation-level one. Option d has incorrect syntax.
A2. b) Rows = subjects (users), Columns = objects (relations). In the access matrix model, subjects (users, accounts, programs) are represented as rows, and objects (relations, views, columns) are represented as columns. Each cell contains the privileges that the subject has on the object.
A3. A must first create a view and then grant SELECT on that view:
CREATE VIEW EmpDept3View AS SELECT NAME, SALARY FROM EMPLOYEE WHERE DNO = 3;
GRANT SELECT ON EmpDept3View TO B;
Note that B gets access to the VIEW, not the EMPLOYEE table directly. B cannot see other columns or employees from other departments.
5. Revoking Privileges and GRANT OPTION
5.1 Revoking Privileges
Sometimes privileges are given temporarily. For example, a contractor needs SELECT access for a specific project. When the project ends, the access should be removed. The REVOKE command handles this.
Syntax: REVOKE <privilege> ON <table> FROM <user>; Example: REVOKE SELECT ON EMPLOYEE FROM STAFF;
This is straightforward — the specified privilege is cancelled for that user. But what happens when that user has passed the privilege to others? That brings us to a very important topic.
5.2 GRANT OPTION and Privilege Propagation
This is one of the most tested concepts in exams. Listen carefully.
When owner A grants a privilege to B, A can give it with or without the GRANT OPTION.
- Without GRANT OPTION: B can use the privilege but CANNOT pass it to anyone else.
- With GRANT OPTION: B can use the privilege AND can also grant it to other users (like C, D, E…).
— Without GRANT OPTION (B cannot propagate) GRANT SELECT ON EMPLOYEE TO B;— With GRANT OPTION (B can propagate to others) GRANT SELECT ON EMPLOYEE TO B WITH GRANT OPTION;
5.3 Cascading Revocation — The Critical Concept
Now here is where it gets interesting. Suppose A grants SELECT with GRANT OPTION to B. B then grants SELECT to C. Now A decides to revoke the privilege from B.
The system automatically revokes all privileges that were propagated based on the revoked privilege. The owner does not need to manually revoke from C — it happens automatically.
5.4 Complete Worked Example
Let me walk you through the full example from your textbook with detailed explanation at each step.
• GRANT OPTION allows the receiver to pass the privilege further
• Without GRANT OPTION = use only, no propagation
• Cascading revocation: When A revokes from B, all users who received the privilege through B also lose it automatically
• To restrict access to specific columns or rows, use views + GRANT on the view
Q1 (MCQ). A grants SELECT with GRANT OPTION to B. B grants SELECT (without GRANT OPTION) to C. C grants SELECT to D. If A revokes SELECT from B, which users lose the privilege?
a) Only B
b) B and C only
c) B, C, and D
d) Only C and D
Q2 (MCQ). A grants UPDATE on EMPLOYEE (SALARY) to B. Which of the following can B do?
a) Update any column of EMPLOYEE
b) Update only the SALARY column of EMPLOYEE
c) Update SALARY in any table
d) Grant UPDATE on SALARY to other users
Q3. Explain why cascading revocation is necessary in DAC systems. What would go wrong without it?
Q4 (True/False). When A revokes a privilege from B, the DBMS must manually check and separately revoke from all users who received the privilege from B.
A1. c) B, C, and D. The cascade goes all the way down the chain. B loses the privilege because A revoked it. C loses it because C got it from B (and B no longer has it). D loses it because D got it from C (and C no longer has it). Even though C did NOT have GRANT OPTION, C still managed to grant to D — wait, actually C cannot grant to D without GRANT OPTION. Let me correct: if C granted to D, it means C DID have GRANT OPTION. In that case, all three lose it. If C did NOT have GRANT OPTION, then C could not have granted to D in the first place, so D never had the privilege. The correct answer assuming C had GRANT OPTION is c) B, C, and D.
A2. b) Update only the SALARY column of EMPLOYEE. The syntax “GRANT UPDATE ON EMPLOYEE (SALARY) TO B” restricts the UPDATE privilege to only the SALARY column of the EMPLOYEE table. B cannot update other columns of EMPLOYEE, cannot update SALARY in other tables, and cannot grant the privilege to others (no GRANT OPTION was given).
A3. Cascading revocation is necessary to prevent orphaned privileges. Without cascading revocation, if A revokes a privilege from B, but C (who got it from B) keeps it, then C has a privilege that traces back to A’s original grant through B — but B no longer has it. This creates an inconsistency: the privilege chain is broken, yet C still holds the privilege. The original owner A has lost control over who has access to their data. Cascading revocation ensures that the owner’s revocation decision is fully respected throughout the entire propagation chain.
A4. False. The DBMS performs cascading revocation automatically. The system tracks the grant dependency graph and automatically revokes all downstream privileges when a privilege is revoked. No manual intervention or separate REVOKE commands are needed for the propagated privileges.
6. Mandatory Access Control (MAC)
Now let’s switch to a completely different philosophy. In DAC, the data owner decides who gets access. In Mandatory Access Control (MAC), the system enforces access based on security classifications. The user has no discretion — the rules are mandatory.
MAC is an all-or-nothing method: a user either has a certain privilege or does not. There is no “maybe.” MAC enforces multi-level security by classifying both data and users into security classes (levels).
6.1 Security Classification Levels
The typical security classes, from highest to lowest, are:
MAC fully depends on the policy of the institution. A military organization might have strict TS/S/C/U levels, while a university might use different classifications.
6.2 The Bell-LaPadula Model
The most commonly used model for multilevel security is the Bell-LaPadula model. It classifies each subject (user, account, program) AND each object (relation, tuple, column, view) into one of the security classifications.
Worked Example from your textbook:
| Security Level | Table (Data) | Users Who Can Access |
|---|---|---|
| Unclassified (U) | Stud-detail | Student, Staff, Dean, P and VP |
| Confidential (C) | Stud-grade-sheet | Staff, Dean, P and VP |
| Secret (S) | Dept-detail, Staff-detail | Dean, P and VP |
| Top Secret (TS) | Salary-detail | P and VP only |
Let me explain this table carefully. The Stud-detail table is Unclassified — the lowest level — so everyone can access it. The Salary-detail table is Top Secret — the highest level — so only the President (P) and Vice President (VP) can access it. A Student cannot see grade sheets (Confidential), department details (Secret), or salary details (Top Secret). A Staff member can see grade sheets but not department or salary details.
GRANT INSERT, DELETE, MODIFY, SELECT ON Stud-detail TO Student, Staff, Dean, P, VP; GRANT INSERT, DELETE, MODIFY, SELECT ON Stud-grade-sheet TO Staff, Dean, P, VP; GRANT INSERT, DELETE, MODIFY, SELECT ON Dept-detail, Staff-detail TO Dean, P, VP; GRANT INSERT, MODIFY ON Salary-detail TO P, VP;
Notice the last line: for Top Secret data, even P and VP only get INSERT and MODIFY — not DELETE. The institution’s policy restricts even top-level users from deleting salary records.
6.3 Key Characteristics of MAC
- All-or-nothing: No partial access based on owner’s choice
- System-enforced: The DBMS enforces the rules, not the data owner
- Prevents illegal information flow: Ensures a high degree of protection
- Rigid: Not flexible — difficult to adapt to different situations
Q1 (MCQ). In a MAC system with levels TS > S > C > U, a user classified as “Secret” wants to read data classified as “Confidential.” What happens?
a) Access is denied because the user’s level is higher
b) Access is granted because the user’s level is higher than or equal to the data’s level
c) Access is granted only if the user has GRANT OPTION
d) Access depends on the data owner’s discretion
Q2 (MCQ). Which of the following is NOT a characteristic of Mandatory Access Control?
a) It is an all-or-nothing method
b) It classifies data and users into security levels
c) The data owner decides who gets which privileges
d) It prevents illegal flow of information
Q3. A university has four tables: Course-catalog (U), Student-grades (C), Faculty-salary (S), and Research-grants (TS). List which users (Student, Faculty, Dept-Head, Dean) can access each table under MAC.
A1. b) Access is granted because the user’s level is higher than or equal to the data’s level. In the Bell-LaPadula model, a subject can read an object if the subject’s security level is greater than or equal to the object’s level. Here, Secret (S) ≥ Confidential (C), so access is granted. This is known as the “no read down” rule — you can only read at or above your level. Option d is wrong because MAC is system-enforced, not owner-discretionary.
A2. c) The data owner decides who gets which privileges. This is the defining characteristic of DAC, NOT MAC. In MAC, the system enforces access based on security levels — the data owner has no discretion. Options a, b, and d are all correct characteristics of MAC.
A3.
Course-catalog (U): Student, Faculty, Dept-Head, Dean (all users can access Unclassified)
Student-grades (C): Faculty, Dept-Head, Dean (Confidential and above)
Faculty-salary (S): Dept-Head, Dean (Secret and above)
Research-grants (TS): Dean only (Top Secret — highest level)
The pattern: each higher security level excludes the users at the level below it. Students only see U. Faculty sees U and C. Dept-Head sees U, C, and S. Dean sees everything.
7. Comparing DAC and MAC
This is a very common exam question. Let me give you a thorough comparison.
| Feature | DAC (Discretionary) | MAC (Mandatory) |
|---|---|---|
| Who decides access? | Data owner has discretion | System enforces based on levels |
| Flexibility | High flexibility — suitable for many applications | Rigid — only for limited environments |
| Protection level | Vulnerable to malicious attacks (e.g., Trojan horses) | High degree of protection — prevents illegal information flow |
| Mechanism | GRANT and REVOKE commands | Security classification levels (TS, S, C, U) |
| Granularity | Can control at column, row, view level | Typically at relation/tuple level |
| Practical use | Preferred in most practical situations | Limited to military, government, high-security environments |
| Trojan horse vulnerability | Yes — main drawback | No — prevented by multilevel security |
Let me explain the Trojan horse issue since it appears in exams. In DAC, suppose user A has high privileges. A malicious user B creates a program (Trojan horse) that A runs. When A runs the program, it executes with A’s privileges and can read sensitive data, then write it to a file that B can access. MAC prevents this because the data is classified at a level that B’s program cannot access, regardless of who runs it.
Q1 (MCQ). A company is choosing between DAC and MAC for its employee database. The company needs flexible access control where department managers can decide who sees their team’s data. Which should they choose and why?
a) MAC, because it prevents Trojan horse attacks
b) DAC, because it provides high flexibility and lets data owners decide access
c) MAC, because it is an all-or-nothing method
d) Neither — they should use encryption only
Q2 (True/False). MAC policies are vulnerable to Trojan horse attacks embedded in application programs.
Q3. Why are discretionary policies preferred in many practical situations despite being less secure than mandatory policies?
A1. b) DAC, because it provides high flexibility and lets data owners decide access. The key requirement is that “department managers can decide who sees their team’s data” — this is exactly what DAC provides (owner discretion). MAC would not work well here because MAC is rigid and system-enforced, not manager-discretionary. While MAC is more secure, it doesn’t fit the flexibility requirement.
A2. False. MAC policies are specifically designed to prevent Trojan horse attacks. It is DAC policies that are vulnerable to Trojan horses. In DAC, a Trojan horse can exploit the user’s privileges to access and leak data. In MAC, the security classification prevents data from flowing to lower-classification users, even through a Trojan horse.
A3. Discretionary policies offer a better trade-off between security and applicability. While they are less secure than mandatory policies (vulnerable to Trojan horses), they are highly flexible and can be adapted to a wide variety of application domains. Mandatory policies are too rigid for most business environments — classifying all data and users into strict security levels is impractical for typical organizations. In most real-world situations, the flexibility of DAC is more important than the extreme security of MAC.
8. Role-Based Access Control (RBAC)
Now let me introduce you to a modern approach that has become very popular. Role-Based Access Control (RBAC) emerged in the 1990s as a proven technology for managing security in large-scale enterprise systems.
The basic idea is beautifully simple: instead of granting privileges directly to users, you grant privileges to roles, and then assign users to those roles.
8.1 RBAC Concepts
Roles are created using CREATE ROLE and removed using DESTROY ROLE commands. The same GRANT and REVOKE commands from DAC are used to assign and revoke privileges from roles.
Role hierarchy is a natural way to organize roles to reflect the organization’s authority structure. For example:
If a user is assigned the “Clerk” role, they automatically get all privileges of both “Clerk” and “Manager” and “VP” through the hierarchy.
8.2 Temporal Constraints
RBAC systems can also include temporal constraints on roles:
- Time and duration of role activations: A “Night-Shift-Operator” role is only active from 10 PM to 6 AM
- Timed triggering: Activating one role automatically triggers another role. For example, activating “Emergency-Responder” role automatically activates “Data-Access-Extended” role
8.3 RBAC vs DAC vs MAC
• Scalability: Managing 1000 users through roles is easier than individual GRANTs
• Web-based applications: RBAC addresses key security requirements of web apps that DAC and MAC cannot handle well
• Flexibility with control: More structured than DAC, more flexible than MAC
• Temporal support: DAC and MAC lack temporal constraint capabilities
• Easier administration: When someone changes jobs, just change their role assignment
• RBAC = permissions associated with roles, users assigned to roles
• Role hierarchy = inheritance of privileges from parent to child roles
• CREATE ROLE and DESTROY ROLE commands
• GRANT/REVOKE used on roles (same SQL as DAC)
• Temporal constraints: time-based activation and triggering
• RBAC is a viable alternative to both DAC and MAC
Q1 (MCQ). In RBAC, if the “Supervisor” role inherits from the “Manager” role, and “Manager” inherits from “Employee,” which privileges does a user assigned to “Supervisor” have?
a) Only the privileges explicitly granted to “Supervisor”
b) Privileges of “Supervisor” and “Manager” only
c) Privileges of “Supervisor,” “Manager,” and “Employee”
d) Only the privileges of “Employee”
Q2 (MCQ). Which RBAC feature allows a role to be active only during specific time periods?
a) Role hierarchy
b) GRANT OPTION
c) Temporal constraints
d) Access matrix model
Q3. A company has 50 accountants who all need the same privileges. Explain why RBAC is better than DAC for this scenario.
A1. c) Privileges of “Supervisor,” “Manager,” and “Employee.” In role hierarchy, a child role inherits ALL privileges from all its ancestor roles. “Supervisor” inherits from “Manager,” which inherits from “Employee.” So a user in “Supervisor” gets the union of all three roles’ privileges.
A2. c) Temporal constraints. Temporal constraints in RBAC allow specifying when roles can be activated (time of day, duration) and how roles can trigger other roles. Role hierarchy is about privilege inheritance, not timing. GRANT OPTION is a DAC concept. The access matrix is a DAC model.
A3. With DAC, the DBA would need to issue 50 separate GRANT commands (one per accountant) for each privilege. If a new privilege is needed, the DBA must issue 50 more GRANT commands. If an accountant leaves, the DBA must issue REVOKE commands. With RBAC, the DBA creates one “Accountant” role, grants all needed privileges to that role ONCE, and then assigns all 50 accountants to that role. Adding a privilege requires one GRANT to the role (automatically applies to all 50). Removing an accountant requires one unassignment from the role. This is far more efficient and less error-prone.
9. Statistical Database Security
Here is our final topic, and it is a very interesting one. Statistical databases are used to produce statistics on various populations. Think of a census database or a medical research database.
The database contains confidential data on individuals (like income, medical conditions). Users are NOT allowed to retrieve individual data. They are only allowed to retrieve statistical information about populations.
9.1 What is a Population?
A population is a set of tuples (rows) in a relation that satisfy some selection condition. For example: “all employees in department 5” or “all patients with diabetes above age 40.”
9.2 Allowed vs Prohibited Queries
The key idea: users can get COUNT, SUM, AVG, MIN, MAX, STANDARD DEVIATION but they cannot get individual attribute values.
9.3 The Inference Problem
Here is the tricky part. Even if you only allow statistical queries, a clever user might combine multiple queries to deduce individual values. This is the inference problem that inference control (one of our four countermeasures) is designed to prevent.
Example of inference attack:
Statistical database security techniques must prohibit the retrieval of individual data. This is achieved by:
- Prohibiting queries that retrieve individual attribute values
- Allowing only queries with aggregate functions
- Additionally restricting queries that could lead to inference (e.g., rejecting queries where the population size is too small)
Q1 (MCQ). In a statistical database, which type of query should be allowed?
a) SELECT * FROM PATIENT WHERE name = ‘John’
b) SELECT AVG(income) FROM CITIZEN WHERE city = ‘Addis’
c) SELECT salary FROM EMPLOYEE WHERE id = 105
d) SELECT name, income FROM CITIZEN WHERE age > 50
Q2 (MCQ). A user submits two statistical queries: (1) COUNT of employees in department 3 = 1, and (2) AVG salary of employees in department 3 = 15,000. What security problem does this demonstrate?
a) Loss of integrity
b) Covert channel
c) Inference problem
d) Flow control violation
Q3 (List and Explain). List the six statistical aggregate functions that are typically allowed in a statistical database.
Q4 (True/False). If a database only allows aggregate function queries, it is completely safe from individual data disclosure.
A1. b) SELECT AVG(income) FROM CITIZEN WHERE city = ‘Addis’. This is a statistical query because it uses an aggregate function (AVG) and returns a summary value for a population, not individual records. Options a, c, and d all retrieve individual attribute values (specific names, specific salaries), which are prohibited in a statistical database.
A2. c) Inference problem. The user deduced that there is exactly one employee in department 3 (COUNT=1) and then used the average salary (AVG=15,000) to determine that individual’s exact salary. This is a classic inference attack — combining multiple statistical queries to reveal individual data. The inference control countermeasure should have blocked this (e.g., by rejecting queries where the population size is 1).
A3. The six statistical aggregate functions allowed in a statistical database are:
1. COUNT — counts the number of tuples in a population
2. SUM — calculates the total of a numeric attribute
3. AVERAGE (AVG) — calculates the mean value
4. MINIMUM (MIN) — finds the smallest value
5. MAXIMUM (MAX) — finds the largest value
6. STANDARD DEVIATION (STDDEV) — measures the spread of values
A4. False. Even with only aggregate queries allowed, individual data can still be disclosed through inference attacks. As shown in the example, combining multiple aggregate queries (especially when populations are small) can reveal individual values. This is why additional inference control techniques (like minimum population size requirements, query set size control, and query overlap restrictions) are needed on top of simply restricting query types.
Quick Revision Notes – Exam Focus
1. Security Basics
• Authorization = giving users the right to use resources
• Three security goals: Integrity (proper modification), Availability (accessible when needed), Confidentiality (hidden from unauthorized users)
• Four countermeasures: Access control, Inference control, Flow control, Encryption
2. Two Security Mechanism Types
• Mandatory (MAC): System enforces based on security levels (TS ≥ S ≥ C ≥ U)
3. Flow Control
• Simple flow policy: two classes — Confidential (C) and Non-confidential (N)
• Only blocked flow: C → N
• Covert channels: Hidden pathways that violate security policy
4. Encryption
• Unauthorized users see cipher text (unreadable)
• Authorized users have the key to decrypt
5. DBA Capabilities
• Account Creation → Access Control
• Privilege Granting → Discretionary Security
• Privilege Revocation → Discretionary Security
• Security Level Assignment → Mandatory Security
6. DAC — Account-Level Privileges
| Privilege | What It Does | SQL Keyword |
|---|---|---|
| Create tables | Create base relations | CREATETAB |
| Create views | Create virtual relations | CREATE VIEW |
| Alter | Add/remove attributes | ALTER |
| Drop | Delete relations/views | DROP |
| Modify | Insert, delete, update tuples | MODIFY |
| Select | Retrieve data | SELECT |
7. DAC — Relation-Level Privileges
• MODIFY on R → divided into UPDATE, DELETE, INSERT (can restrict to specific columns)
• REFERENCES on R → reference R in integrity constraints (can restrict to specific columns)
• Syntax: GRANT operation ON table TO user;
• Syntax: REVOKE operation ON table FROM user;
8. Access Matrix Model
• Columns = Objects (relations, views)
• Cell M(i,j) = privileges of subject i on object j
9. Views for Privilege Restriction
• Restrict rows: Create view with WHERE condition
• Grant privilege on the VIEW, not the original table
• Requirement: creator must have SELECT on all relations in the view definition
10. GRANT OPTION
• WITH GRANT OPTION → receiver can pass privilege to others
• WITHOUT GRANT OPTION → receiver can use but NOT pass
• Cascading revocation: Revoking from B automatically revokes from all who received through B
• The DBMS handles cascading automatically
11. MAC — Bell-LaPadula Model
• System-enforced (not discretionary)
• All-or-nothing method
• Prevents illegal information flow
• Rigid — limited to specific environments
12. DAC vs MAC Comparison
| Aspect | DAC | MAC |
|---|---|---|
| Flexibility | High | Low (rigid) |
| Trojan horse protection | Vulnerable | Protected |
| Practical use | Preferred | Limited |
| Who controls | Data owner | System |
13. RBAC
• CREATE ROLE / DESTROY ROLE
• GRANT/REVOKE on roles
• Role hierarchy: child inherits parent’s privileges
• Temporal constraints: time-based activation, role triggering
• Best for large-scale enterprise and web applications
14. Statistical Database Security
• Prohibited: Queries that retrieve individual attribute values
• Inference problem: Combining queries to deduce individual data
• Inference control is the countermeasure
Common Mistakes in Exams
❌ Mistake 2: Saying “GRANT OPTION allows the user to grant ANY privilege” → NO, only the specific privilege granted
❌ Mistake 3: Forgetting cascading revocation — when A revokes from B, ALL downstream users also lose the privilege
❌ Mistake 4: Saying MAC is “flexible” → MAC is rigid; DAC is flexible
❌ Mistake 5: Confusing account-level and relation-level privileges — CREATETAB is account-level, SELECT ON table is relation-level
❌ Mistake 6: Saying RBAC uses different GRANT/REVOKE syntax → RBAC uses the SAME SQL commands but on roles
❌ Mistake 7: Saying “statistical databases are safe if only aggregate queries are allowed” → inference attacks are still possible
❌ Mistake 8: Confusing flow control with access control — flow control prevents information flow through ANY channel, access control only controls direct login
Challenge Exam Questions
These questions test your deep understanding. Try them before looking at the answers!
Section A: Multiple Choice Questions
Q1. The DBA grants CREATETAB to user A1. A1 creates table T1 and grants SELECT on T1 to A2 WITH GRANT OPTION. A2 grants SELECT on T1 to A3. A2 grants SELECT on T1 to A4 WITH GRANT OPTION. A4 grants SELECT on T1 to A5. If the DBA revokes CREATETAB from A1, what happens to the SELECT privileges on T1?
a) Only A1 loses all privileges; A2, A3, A4, A5 keep theirs
b) A1, A2, A3, A4, A5 all lose SELECT on T1
c) A1, A2, A3 lose SELECT; A4 and A5 keep theirs because A4 had GRANT OPTION
d) Only A1 and A2 lose SELECT; A3, A4, A5 keep theirs
b) A1, A2, A3, A4, A5 all lose SELECT on T1. When the DBA revokes CREATETAB from A1, A1 loses the ability to create tables. More importantly, since A1 created T1 (and is the owner), revoking A1’s account-level privilege means A1’s ownership privileges are affected, and all privileges derived from A1’s ownership cascade down the entire chain. A2 got it from A1 → lost. A3 got it from A2 → lost (cascade). A4 got it from A2 → lost (cascade). A5 got it from A4 → lost (cascade). The GRANT OPTION held by A4 does not protect downstream users from cascading revocation.
Q2. In a system using a simple two-class flow policy (Confidential and Non-confidential), which of the following information flows would be BLOCKED?
a) From Non-confidential to Confidential
b) From Confidential to Confidential
c) From Non-confidential to Non-confidential
d) From Confidential to Non-confidential
d) From Confidential to Non-confidential. In the simple two-class flow policy, the ONLY blocked flow is from Confidential (C) to Non-confidential (N). All other flows (C→C, N→N, N→C) are permitted. The policy prevents sensitive information from being “downgraded” to a less secure classification.
Q3. A database administrator wants to allow a user to reference the DEPARTMENT table’s DNUMBER column in a foreign key constraint but NOT allow the user to read any data from the DEPARTMENT table. Which privilege should be granted?
a) SELECT ON DEPARTMENT
b) REFERENCES ON DEPARTMENT (DNUMBER)
c) UPDATE ON DEPARTMENT (DNUMBER)
d) This is impossible — referencing requires SELECT privilege
b) REFERENCES ON DEPARTMENT (DNUMBER). The REFERENCES privilege specifically allows a user to reference a relation when specifying integrity constraints (like foreign keys), without granting any data retrieval (SELECT) privileges. By restricting it to the DNUMBER column, the user can only reference that specific column in constraints, not any other columns of the DEPARTMENT table. This is a precise and minimal privilege grant.
Q4. An organization uses RBAC with the following role hierarchy: Dean → Department Head → Faculty → Student. The “Faculty” role has been granted SELECT on the GRADES table. Which users can access the GRADES table?
a) Only users assigned to the Faculty role
b) Users assigned to Faculty, Department Head, and Dean roles
c) Users assigned to Faculty, Department Head, Dean, and Student roles
d) All users in the system
b) Users assigned to Faculty, Department Head, and Dean roles. In role hierarchy, privilege inheritance flows UPWARD. The Dean role inherits from Department Head, which inherits from Faculty. So Dean gets Faculty’s privileges, and Department Head gets Faculty’s privileges. Student is BELOW Faculty in the hierarchy and does NOT inherit from Faculty — Student is a child, not a parent. Therefore, Students do NOT get Faculty’s privileges. Only Faculty, Department Head, and Dean can access GRADES.
Q5. Which of the following is the MOST significant vulnerability of Discretionary Access Control?
a) It cannot restrict access at the column level
b) It is vulnerable to Trojan horse attacks embedded in application programs
c) It requires the Bell-LaPadula model to function
d) It does not support the REVOKE command
b) It is vulnerable to Trojan horse attacks embedded in application programs. This is the main drawback of DAC mentioned in the textbook. Since DAC allows users to decide access based on their privileges, a malicious program (Trojan horse) can exploit a high-privilege user’s access to read sensitive data and write it to a location accessible to a lower-privilege user. MAC prevents this through multilevel security classification. Option a is wrong (DAC supports column-level restriction through views). Option c is wrong (Bell-LaPadula is for MAC, not DAC). Option d is wrong (DAC fully supports REVOKE).
Section B: True/False
Q6. In a statistical database, allowing only aggregate function queries completely eliminates the risk of individual data disclosure.
False. Allowing only aggregate queries does NOT completely eliminate the risk. Users can still perform inference attacks by combining multiple statistical queries to deduce individual values. For example, if a query returns COUNT=1 for a specific condition and then an AVG query is run on the same population, the individual value is directly revealed. This is why additional inference control techniques (such as minimum query set size, suppression, and perturbation) are needed.
Q7. In Mandatory Access Control, the data owner can choose to grant a Secret-level user access to Top Secret data if they trust that user.
False. MAC is called “mandatory” precisely because the data owner has no discretion. Access is enforced by the system based on security classifications. A Secret-level user CANNOT access Top Secret data, regardless of what the data owner wants. The “all-or-nothing” nature of MAC means the system rules are absolute and cannot be overridden by individual owners.
Q8. The REFERENCES privilege allows a user to read data from the referenced table.
False. The REFERENCES privilege only allows a user to reference the table when specifying integrity constraints (such as foreign keys). It does NOT allow reading any data from the table. To read data, the user needs the SELECT privilege. REFERENCES and SELECT are separate, independent privileges.
Q9. A covert channel is a pathway through which information flows in a way that explicitly violates the organization’s security policy.
False. The key word here is “explicitly.” A covert channel allows information to flow implicitly (not explicitly). The information flows through indirect means — like timing variations, CPU usage patterns, or file locking behavior — rather than through direct data transmission. The flow is hidden and violates the security policy, but it does so implicitly, which makes it harder to detect and prevent.
Section C: Fill in the Blanks
Q10. The ________ model is the commonly used model for multilevel security in Mandatory Access Control, and it classifies subjects and objects into security levels where ________ is the highest level.
Bell-LaPadula; Top Secret (TS). The Bell-LaPadula model is the standard for multilevel security. It uses the classification hierarchy TS ≥ S ≥ C ≥ U, where Top Secret is the highest and Unclassified is the lowest.
Q11. In RBAC, the command used to create a new role is ________, and the command used to assign privileges to that role uses the same ________ command from DAC.
CREATE ROLE; GRANT. Roles are created with CREATE ROLE and destroyed with DESTROY ROLE. Once a role exists, the standard SQL GRANT command (from DAC) is used to assign privileges to the role, and REVOKE is used to remove privileges from the role.
Q12. A ________ is a database log used mainly for security purposes, while the process of reviewing it during a specific time period is called ________.
Audit trail; Database audit. The audit trail is the log itself — it records all operations and who performed them for security tracking. Database audit is the process of examining the audit trail to investigate security concerns during a specific period.
Q13. The access matrix model has ________ representing users/accounts and ________ representing relations/views, with each cell containing the ________ that the user holds on the relation.
Rows; Columns; Privileges (or types of privileges). In the access matrix M, rows represent subjects (users, accounts, programs), columns represent objects (relations, views, columns), and each cell M(i,j) contains the types of privileges (SELECT, UPDATE, DELETE, etc.) that subject i holds on object j.
Section D: Short Answer and Workout Questions
Q14. Consider the following sequence of GRANT operations on a table PROJECT owned by user U1:
List all users who lose the SELECT privilege on PROJECT after the REVOKE, and explain the cascading effect for each.
After U1 revokes SELECT on PROJECT from U2, the following users lose the privilege through cascading revocation:
U2: Loses SELECT directly — U1 revoked it from U2.
U3: Loses SELECT — U3 received it from U2, and U2 no longer has it.
U4: Loses SELECT — U4 received it from U2 (with GRANT OPTION), and U2 no longer has it.
U5: Loses SELECT — U5 received it from U4, and U4 no longer has it (because U4’s privilege was cascaded away).
Result: U2, U3, U4, and U5 ALL lose the SELECT privilege on PROJECT. The cascade follows the entire dependency chain: U1→U2→U3 and U1→U2→U4→U5. Every user who received the privilege through U2’s original grant (directly or indirectly) is affected.
Q15. A hospital database has the following tables and security classifications under MAC:
The hospital has four user classifications: Receptionist (U), Nurse (C), Doctor (S), Researcher (TS). For each table, list which users can access it. Then explain why a Doctor cannot access Research-data even though they have a high security level.
Access by table:
Patient-demographics (U): Receptionist, Nurse, Doctor, Researcher (all levels ≥ U)
Patient-diagnosis (C): Nurse, Doctor, Researcher (all levels ≥ C)
Doctor-notes (S): Doctor, Researcher (all levels ≥ S)
Research-data (TS): Researcher only (only level ≥ TS)
Why Doctor cannot access Research-data: In MAC, a subject can only access objects at or below their security level. A Doctor has security level S (Secret). Research-data is classified as TS (Top Secret). Since S < TS, the Doctor’s level is below the data’s classification. MAC rules prevent lower-level subjects from accessing higher-level objects. Only a subject at level TS or above can access TS data. This is the “no read up” rule of the Bell-LaPadula model. Even though S is high, it is not high enough for TS data.
Q16. Explain why RBAC is considered a viable alternative to both DAC and MAC. Give one specific advantage over each.
RBAC is considered a viable alternative to both DAC and MAC because it addresses the limitations of each while capturing some of their strengths.
Advantage over DAC: RBAC is more scalable and easier to administer in large organizations. In DAC, if 500 users need the same privileges, the administrator must manage 500 individual GRANT/REVOKE operations. In RBAC, the admin manages one role and assigns users to it. Also, RBAC reduces the risk of errors — in DAC, forgetting to revoke from one user creates a security hole; in RBAC, removing a user from a role automatically revokes all role privileges. Furthermore, RBAC is less vulnerable to Trojan horse attacks than DAC because privileges are tied to roles with temporal constraints, not directly to user accounts.
Advantage over MAC: RBAC is far more flexible than MAC. MAC’s rigid classification system (TS, S, C, U) is difficult to apply in most organizations. RBAC allows custom roles tailored to the organization’s specific structure and needs. RBAC also supports temporal constraints (time-based activation, role triggering) that MAC completely lacks. RBAC can adapt to changing organizational requirements without reclassifying all data and users, which MAC would require.
Q17. User A owns the EMPLOYEE table and wants to set up the following access for user B: B can only see the NAME and DEPARTMENT columns, and only for employees whose SALARY is greater than 50,000. B should also be able to pass this limited SELECT privilege to other users. Write the exact SQL statements A must execute.
A must first create a view that restricts both columns and rows, then grant SELECT on the view with GRANT OPTION:
CREATE VIEW EmpHighSalary AS
SELECT NAME, DEPARTMENT
FROM EMPLOYEE
WHERE SALARY > 50000;
GRANT SELECT ON EmpHighSalary TO B WITH GRANT OPTION;
Explanation: The view EmpHighSalary restricts access to only the NAME and DEPARTMENT columns (column restriction) and only rows where SALARY > 50000 (row restriction). The GRANT is on the VIEW, not on the EMPLOYEE table directly. WITH GRANT OPTION allows B to pass this restricted SELECT privilege to other users. Anyone who receives the privilege from B will also only see the restricted view — they cannot bypass the view to access the full EMPLOYEE table.
Q18. A statistical database contains citizen income data. A researcher submits the following queries:
Explain how the researcher can deduce individual income values. What inference control technique could prevent this?
Deduction process: From Q1, the researcher knows there are exactly 2 teachers in city X. From Q2, their total income is 180,000. From Q3, the maximum income is 100,000. Using simple arithmetic:
Teacher 1’s income = MAX = 100,000
Teacher 2’s income = SUM – MAX = 180,000 – 100,000 = 80,000
The researcher has deduced the exact individual income of both teachers, even though only statistical (aggregate) queries were allowed. This is a classic inference attack.
Inference control techniques to prevent this:
1. Minimum query set size: Reject any query where the resulting population size is below a threshold (e.g., reject if COUNT < 5). This would reject Q1, Q2, and Q3 since the population size is only 2.
2. Query overlap control: Prevent queries that overlap too much, making it possible to narrow down to individuals.
3. Suppression: Do not return results for small populations — return “query rejected” instead.
4. Perturbation: Add small random noise to the results so exact values cannot be deduced (e.g., return SUM as 181,350 instead of 180,000).