Database Security

User Roles and Privileges

User

An individual or application connecting to the database.
Identified by a username (and usually a password).

Privileges (Permissions)

Specific rights to perform actions on database objects.
Examples:
- System Privileges → CREATE TABLE, CREATE USER, SHUTDOWN (affect the whole DB).
- Object Privileges → SELECT, INSERT, UPDATE, DELETE (affect specific tables, views, procedures).

Roles

A role is a collection of privileges.
Makes privilege management easier (instead of assigning many privileges to each user).
Users are granted roles, and roles contain privileges.

Why Roles and Privileges Matter in Database Design

Security → Protect sensitive data from unauthorized access.
Integrity → Ensure only authorized users can change data (e.g., only HR can update salaries).
Accountability → Track who did what.
Least Privilege Principle → Users should only get the minimum permissions required to perform their tasks.

Example of User Role

Let’s imagine a University Database with three types of users:

Admin → Full control.
Professor → Can view and update grades.
Student → Can only view their own courses/grades.

Step 1: Create Users

-- Create three users
CREATE USER admin_user IDENTIFIED BY 'adminpass';
CREATE USER professor_user IDENTIFIED BY 'profpass';
CREATE USER student_user IDENTIFIED BY 'studentpass';

Step 2: Create Roles

-- Create roles
CREATE ROLE admin_role;
CREATE ROLE professor_role;
CREATE ROLE student_role;

Step 3: Assign Privileges to Roles

-- Admin can do everything
GRANT ALL PRIVILEGES TO admin_role;

-- Professors can SELECT and UPDATE grades table
GRANT SELECT, UPDATE ON Grades TO professor_role;

-- Students can only SELECT (read-only access)
GRANT SELECT ON Grades TO student_role;

Step 4: Assign Roles to Users

-- Assign roles to specific users
GRANT admin_role TO admin_user;
GRANT professor_role TO professor_user;
GRANT student_role TO student_user;

Step 5: Usage Example

If professor_user logs in and tries:

UPDATE Grades SET score = 90 WHERE student_id = 101 AND course_id = 'CS101';

Allowed (since professor has UPDATE privilege).

If student_user tries:

UPDATE Grades SET score = 100 WHERE student_id = 101;

Fails (student only has SELECT privilege).

If student_user runs:

SELECT * FROM Grades WHERE student_id = 101;

Allowed (read-only access).

Advanced Security Features

Column-Level Privileges

Limit access to specific columns.

GRANT SELECT (student_id, course_id) ON Grades TO student_role;

Views for Security

Instead of giving direct access, create views.

CREATE VIEW student_view AS
SELECT student_id, course_id, score
FROM Grades
WHERE student_id = SYS_CONTEXT('USERENV','SESSION_USER');
GRANT SELECT ON student_view TO student_role;

Each student only sees their own grades.

Revoking Privileges
- If a user should no longer access data:
```
REVOKE UPDATE ON Grades FROM professor_role;
```

Data Encryption

Encryption = the process of converting plain text (readable data) into cipher text (unreadable format) using an encryption algorithm and key.

Decryption = converting ciphertext back into plaintext with the correct key.

The purpose: even if unauthorized users access the database or backups, they cannot read sensitive data without the decryption key.

Types of Encryption in Databases

Encryption at Rest
- Protects stored data (disk files, backups, logs).
- If someone steals the physical storage, data remains unreadable.
- Example: Transparent Data Encryption (TDE) in Oracle, SQL Server, PostgreSQL.
Encryption in Transit
- Protects data moving between client ↔ database server over the network.
- Uses protocols like TLS/SSL.
Column-Level or Field-Level Encryption
- Encrypts specific sensitive columns in tables (e.g., passwords, credit card numbers).
- Provides fine-grained protection but may impact performance.
Application-Level Encryption
- Application encrypts data before inserting into DB and decrypts after fetching.
- Database only stores ciphertext.

Example of Column-Level Encryption

Suppose we design a Banking Database where credit_card_number must be encrypted.

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    credit_card_number VARBINARY(256) -- store as encrypted
);

Insert with Encryption:

-- Create a symmetric key
CREATE SYMMETRIC KEY CreditCardKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'StrongPassword123';

-- Open the key
OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY PASSWORD = 'StrongPassword123';

-- Insert encrypted value
INSERT INTO Customers (customer_id, name, credit_card_number)
VALUES (1, 'Alice', EncryptByKey(Key_GUID('CreditCardKey'), '4111111111111111'));

The card number is stored in the database as ciphertext, not plain text.

Decrypt when Reading:

-- Decrypt the credit card number
SELECT name,
       CONVERT(VARCHAR, DecryptByKey(credit_card_number)) AS credit_card_number
FROM Customers;

Only users with the right key/password can view the real number.

SQL Injection

SQL Injection (SQLi) happens when untrusted input is concatenated directly into an SQL query, allowing attackers to manipulate the query.

Example of a vulnerable query (string concatenation):

-- Suppose 'username' and 'password' come directly from user input
SELECT * FROM Users
WHERE username = ' " + user_input + " '
AND password = ' " + pass_input + " ';

If an attacker enters:

username = 'admin'
password = "' OR '1'='1"

The query becomes:

SELECT * FROM Users
WHERE username = 'admin'
AND password = '' OR '1'='1';

'1'='1' is always true, so the attacker logs in without a valid password.

SQL Injection Prevention at Database Design Level

When designing a secure database system, you don’t just rely on the application — you enforce controls at multiple layers:

Use Parameterized Queries / Prepared Statements

Instead of embedding input directly into SQL, use placeholders (? or :param).
DB engine treats inputs as data, not executable SQL.

Example in Python (safe with placeholders):

cursor.execute(
    "SELECT * FROM Users WHERE username = %s AND password = %s",
    (username, password)
)

Here, even if password = "' OR '1'='1", it is treated as a string, not SQL code.

Stored Procedures with Parameters

Encapsulate queries in stored procedures, and only allow execution via defined parameters.

CREATE PROCEDURE AuthenticateUser (@username NVARCHAR(50), @password NVARCHAR(50))
AS
BEGIN
    SELECT * FROM Users
    WHERE username = @username AND password = @password;
END;

Prevents direct manipulation of SQL strings.

Use the Principle of Least Privilege

Application accounts should have only the privileges they need.
Example:
- The web app should only SELECT/INSERT into Users.
- It should NOT have DROP TABLE, ALTER, or GRANT privileges.

GRANT SELECT, INSERT, UPDATE ON Users TO app_user;

Even if SQL injection happens, the attacker’s damage is limited.

Input Validation & Constraints at Schema Level

Use CHECK constraints, data types, NOT NULL, foreign keys to enforce valid inputs.
Example:

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash CHAR(64) NOT NULL,
    email VARCHAR(100) CHECK (email LIKE '%@%')
);

Even if injected input reaches DB, invalid formats get rejected.

Use Views for Restricted Access

Instead of exposing sensitive tables directly, create views with limited columns.
Example:

CREATE VIEW SafeUsers AS
SELECT user_id, username, email
FROM Users;
GRANT SELECT ON SafeUsers TO app_user;

Prevents SQL injection from exposing sensitive fields (like password hashes).

User Roles and Privileges​

Why Roles and Privileges Matter in Database Design​

Example of User Role​

Advanced Security Features​

Data Encryption​

Types of Encryption in Databases​

Example of Column-Level Encryption​

SQL Injection​

SQL Injection Prevention at Database Design Level​

Use Parameterized Queries / Prepared Statements​

Stored Procedures with Parameters​

Use the Principle of Least Privilege​

Input Validation & Constraints at Schema Level​

Use Views for Restricted Access​