Database Access Control
✅ Database Access Control –
1. Definition
o Database access control manages who can access and what actions they can
perform on database data.
o It protects confidential information from unauthorized users.
2. Purpose
o To permit access to authorized users.
o To restrict access to unauthorized individuals.
o Forms a core component of overall database security.
3. Access Control Mechanisms
o Built into both commercial and open-source DBMSs.
o Control actions even after authentication is successful.
o Provide an additional layer of security.
4. Integration with Authentication
o Works in tandem with login-based authentication.
o Just logging in doesn’t grant full access — users are limited based on their
permissions.
5. Granular Control
o Users are only allowed to interact with specific parts of the database (e.g., certain
tables, views, or procedures).
o Prevents misuse or accidental modification of sensitive data.
6. Compliance with Regulations
o Helps meet legal requirements like:
GDPR – for personal data protection in Europe.
HIPAA – for health data privacy in the U.S.
o Ensures governance over data access and handling.
✅ Access Control Matrix – Explained
7. What is it?
o A tabular structure used by DBAs to plan and assign access.
o Columns = database objects (e.g., tables, views, procedures).
o Rows = subjects (e.g., users, roles, or groups).
o Cells = type of access (e.g., READ, INSERT, UPDATE, DELETE, EXECUTE).
8. Example Permissions
o User U101 can READ and UPDATE Student table, EXECUTE WrapUp
procedure.
o Advisor role can READ and WRITE to several tables, but cannot CREATE
TABLES.
9. Enforcement
o Once planned, access rights are implemented using authorization languages
specific to the DBMS (like SQL GRANT and REVOKE).
✅ Database Administrator's Role
10. Responsibilities
o Create and modify the structure of the database.
o Use authorization tools to grant or revoke access rights.
11. Delegation of Authority
o DBAs can allow certain users to authorize others.
o Useful for scaling access control but can be risky.
12. Security Risk of Multiple Authorizers
o Too many authorizers may cause loss of control.
o Makes it harder for DBAs to track and revoke access if needed.
Here's a point-wise explanation of the Types of Administrative Policies for Database Access,
based on your input — suitable for a long (16-mark) answer:
✅ Types of Administrative Policies for Database Access
A well-designed Database Management System (DBMS) provides flexible ways to manage how
access rights are granted and monitored. The main types of administrative access control
policies are:
1. Centralized Administration
Definition:
In this policy, a small, trusted group (usually DBAs) has full control over granting and
revoking access rights throughout the entire database.
Features:
o All access rights are managed from a single authority.
o Ensures consistency in enforcing security policies.
o Makes auditing and monitoring access easier.
o Reduces the chance of configuration errors or unauthorized permission grants.
Advantages:
o Tighter control over who accesses what.
o Reduces the risk of accidental or malicious breaches.
o Ideal for large organizations with sensitive data.
Example:
A large financial institution where all customer records are stored in multiple databases.
DBAs at the central IT office are the only ones allowed to manage access permissions
across all departments.
2. Decentralized Administration
Definition:
Authority to grant access is distributed among different departments, roles, or users —
often based on domain or department-specific data.
Features:
o Multiple administrators can grant access within their scope.
o Common in organizations with multiple branches or departments.
Advantages:
o Faster access control decisions within departments.
o Empowers teams to manage their own data access.
o Useful in dynamic environments where data access needs change frequently.
Disadvantages:
o Risk of inconsistency in permission settings.
o Harder to monitor and audit system-wide access.
Example:
In a university, department heads manage access to their department’s database (e.g.,
admissions, academics, exams), without needing central IT intervention.
3. Role-Based Administration
Definition:
Access rights are assigned based on roles rather than individual users. Roles represent job
functions (e.g., student, teacher, accountant), and each role has specific permissions.
Features:
o Users are assigned roles, and roles are assigned privileges.
o Simplifies administration — no need to define access individually.
o Ensures policy compliance across users performing the same role.
Advantages:
o Easy to add or remove users by just assigning roles.
o Promotes principle of least privilege (only necessary access is given).
o Highly scalable and manageable.
Example:
In a hospital, doctors, nurses, and billing staff each have predefined roles. When a new
nurse joins, assigning them the “nurse” role automatically gives them correct access
rights.
Here’s a point-wise explanation of the main Access Control Models — RBAC, ABAC, and
MAC — which are essential for securing databases and systems. This is suitable for a 16-mark
answer.
✅ Access Control Models in Database Security
Access control models define how permissions are granted to users or entities in a system.
These models ensure that only authorized users can access specific resources under defined
conditions.
The most widely used models are:
1. Role-Based Access Control (RBAC)
Definition:
In RBAC, access permissions are assigned to roles (like admin, teacher, student), and
users are assigned to roles. Users gain access based on their role.
Key Components:
o Users: Individuals who access the system.
o Roles: Job functions (e.g., Doctor, Clerk).
o Permissions: Actions allowed (e.g., READ, WRITE).
o Sessions: The user’s active role during use.
Advantages:
o Easy to manage in large organizations.
o Supports least privilege principle.
o Efficient for systems with many users and few roles.
Example:
In a university database, a "Faculty" role can access student grades, while a "Student"
role can only view their own records.
2. Attribute-Based Access Control (ABAC)
Definition:
ABAC uses attributes (properties) to grant access. Attributes can be related to the user,
resource, environment, or action.
Key Elements:
o User attributes: e.g., department, clearance level.
o Resource attributes: e.g., classification level.
o Environment attributes: e.g., time of day, location.
o Policies: Logical rules that use these attributes.
Advantages:
o Highly granular and flexible.
o Suitable for dynamic environments (e.g., cloud, IoT).
o Supports context-aware access control.
Example:
A user from the HR department can access employee records only during working hours
and only from the office network.
3. Mandatory Access Control (MAC)
Definition:
MAC is a strict model where access rights are assigned based on security labels or
classifications. Users cannot change access permissions.
Key Features:
o Policies are enforced by the system.
o Often used in military and government systems.
o Users and data have labels like Top Secret, Confidential.
Advantages:
o Strong control over confidential data.
o Prevents unauthorized information flow.
Example:
A user with "Secret" clearance cannot access "Top Secret" files, even if they try to.
✅ Granularity of Access Rights in Database Security
What is Granularity?
Granularity refers to the level of detail at which access control is applied in a database.
The more granular the access control, the finer and more specific the permissions that can be set for
different users or roles.
Types of Granularity Levels
1. Database-Level Access
Grants or restricts access to the entire database.
Useful for managing high-level access (e.g., DBAs).
✅ Example: A database administrator can access all databases in the system.
2. Table-Level Access
Access is controlled per table.
A user may access one table but be restricted from another.
✅ Example: A student can view the "Results" table but not the "Faculty" table.
3. Column-Level Access
Permissions are defined for individual columns within a table.
Helps protect sensitive fields like salary, SSN, passwords.
✅ Example: HR staff can access employee names but not salary columns.
4. Row-Level Access
Access is controlled based on specific rows/records in a table.
Often based on the user’s identity or role.
✅ Example: A teacher can only view grades of students in her own class.
5. Cell-Level Access (Fine-Grained Access Control)
Access is managed at the individual data cell level (very fine granularity).
Provides the highest level of control but is more complex.
✅ Example: A user can see only selected data cells in a medical record based on privacy levels
Typical Access Rights in a Database
Database Management Systems (DBMS) provide various access control rights to manage how users
interact with data. These rights ensure security, data integrity, and controlled access.
1. SELECT
Purpose: Allows users to read/view data from one or more tables.
Use Case: Common for read-only users, analysts, and reporting tools.
Example: A dashboard that pulls sales data from a table to generate monthly reports.
2. INSERT
Purpose: Allows users to add new rows into a table.
Use Case: Used in applications or interfaces that collect user input.
Example: A customer support form where new complaints are inserted into the
“Support_Log” table.
3. UPDATE
Purpose: Enables users to modify existing data in a table.
Use Case: Combined with SELECT for users who need to edit records.
Example: A user updating their address or phone number in a banking system.
4. DELETE
Purpose: Allows users to remove rows from a table.
Use Case: Given only to trusted users due to risk of data loss.
Example: An admin deleting outdated product entries from an inventory table.
5. REFERENCES
Purpose: Allows users to create foreign key relationships with other tables.
Use Case: Maintains referential integrity between related data.
Example: In an e-commerce database, a foreign key from OrderDetails referencing the
Products table ensures that only existing products are used in orders
