Database Security Best Practices

Welcome to Week 3 of the Database Decoded series! After mastering stored procedures and automation, we’re shifting focus to one of the most critical aspects of database management: security. Today, you’ll learn how to protect your data from unauthorized access, breaches, and attacks. Let’s dive into user roles, encryption, and defending against SQL injection.

Why Security Matters

Data breaches cost companies millions annually. Security isn’t just about compliance—it’s about safeguarding sensitive information like:

User passwords and personal details.
Financial records (e.g., credit card numbers).
Confidential business data.

A single vulnerability can lead to catastrophic leaks. Let’s lock things down!

Topics Covered

1. User Roles and Permissions

Granting blanket access to your database is risky. Instead, use roles to enforce the principle of least privilege (users get only the access they need).

Step 1: Create Roles

-- Create a role for HR staff  
CREATE ROLE HR_Team;  

-- Create a role for read-only reporting  
CREATE ROLE Report_Viewer;

Step 2: Assign Permissions

-- Let HR_Team view and edit Employees table  
GRANT SELECT, INSERT, UPDATE ON Employees TO HR_Team;  

-- Let Report_Viewer only read data  
GRANT SELECT ON Employees TO Report_Viewer;  
GRANT SELECT ON Departments TO Report_Viewer;

Step 3: Add Users to Roles

-- Add user "JohnDoe" to HR_Team  
ALTER ROLE HR_Team ADD MEMBER JohnDoe;

Best Practice: Regularly audit roles with:

-- List all roles and members  
EXEC sp_helprolemember;

2. Encryption: Protecting Data at Rest and in Transit

Encryption ensures data is unreadable to unauthorized users, even if they access the database files.

Transparent Data Encryption (TDE)

Encrypts the entire database.

-- Enable TDE (requires master key and certificate)  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  

ALTER DATABASE TestDB  
SET ENCRYPTION ON;

Column-Level Encryption

Encrypt sensitive columns (e.g., passwords).

-- Create a column master key  
CREATE COLUMN MASTER KEY MyColumnMasterKey  
WITH (KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',  
      KEY_PATH = 'CurrentUser/My/AA1234567890ABCDEF');  

-- Encrypt the "Salary" column  
CREATE COLUMN ENCRYPTION KEY MyColumnEncryptionKey  
WITH VALUES (  
    COLUMN_MASTER_KEY = MyColumnMasterKey,  
    ALGORITHM = 'RSA_OAEP',  
    ENCRYPTED_VALUE = 0x0143...  
);  

ALTER TABLE Employees  
ALTER COLUMN Salary VARCHAR(60)  
ENCRYPTED WITH (  
    ENCRYPTION_TYPE = DETERMINISTIC,  
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',  
    COLUMN_ENCRYPTION_KEY = MyColumnEncryptionKey  
);

3. Preventing SQL Injection Attacks

SQL injection occurs when attackers insert malicious code into your queries.

Vulnerable Code Example

-- UNSAFE: User input concatenated directly  
DECLARE @UserInput NVARCHAR(100) = '1; DROP TABLE Employees;--';  
EXEC('SELECT * FROM Employees WHERE EmployeeID = ' + @UserInput);

Result: The Employees table is deleted!

Solution: Parameterized Queries

-- SAFE: Use parameters to sanitize inputs  
DECLARE @UserInput NVARCHAR(100) = '1; DROP TABLE Employees;--';  
SELECT * FROM Employees WHERE EmployeeID = @UserInput;

Parameters treat input as data, not executable code.

Practice Tasks

Task 1: Create and Test Roles

Create a role Audit_Team with read-only access to an AuditLog table.
Restrict them from modifying data.

Task 2: Encrypt a Column

Encrypt the Email column in the Employees table using column-level encryption.
Test inserting and querying encrypted data.

Task 3: Block SQL Injection

Write a stored procedure to fetch employees by DepartmentID using parameters.
Attempt to inject malicious code (e.g., '1 OR 1=1') and verify it fails.

Key Takeaways

Roles & Permissions: Limit access using the principle of least privilege.
Encryption: Protect data with TDE (entire database) or column-level encryption.
SQL Injection: Always use parameterized queries or stored procedures.
Audit Regularly: Review user permissions and encryption keys.