Week 4, Day 1: Advanced Database Security

Welcome to Day 1 of Database Decoded Week 4! Today, we’re diving into advanced database security—a critical skill for protecting sensitive data and meeting compliance requirements. You’ll learn how to restrict access at the row level, mask sensitive information, and track every change to your database. Let’s secure your data like a pro!

Why Advanced Security Matters

Data breaches are costly—both financially and reputationally. Advanced security measures help you:

• Protect sensitive data: Ensure only authorized users can access specific rows or columns.

• Meet compliance standards: Adhere to regulations like GDPR, HIPAA, and CCPA.

• Track changes: Maintain an audit trail for accountability and forensic analysis.

Let’s explore three powerful tools: Row-Level Security (RLS), Dynamic Data Masking (DDM), and Auditing.

Topics Covered

1. Row-Level Security (RLS)

RLS restricts access to specific rows in a table based on user roles or permissions. It’s perfect for scenarios where different users should see different subsets of data.

Real-World Example: Allow managers to view only their team’s data.

Step 1: Create a Filter Function
This function defines the logic for filtering rows.

CREATE FUNCTION dbo.fn_TeamFilter(@EmployeeID INT)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN SELECT 1 AS Access  
WHERE @EmployeeID = USER_ID();  

• Explanation:

  • @EmployeeID is compared to the current user’s ID (USER_ID()).

  • If they match, the user can access the row.

Step 2: Create a Security Policy
Apply the filter function to the Employees table.

CREATE SECURITY POLICY TeamFilter  
ADD FILTER PREDICATE dbo.fn_TeamFilter(EmployeeID) ON dbo.Employees  
WITH (STATE = ON);  

• Explanation:

  • ADD FILTER PREDICATE links the function to the table.

  • WITH (STATE = ON) enables the policy immediately.

Result:

• A manager with EmployeeID = 101 can only see rows where EmployeeID = 101.

• Other rows are invisible to them.

2. Dynamic Data Masking (DDM)

DDM hides sensitive data from unauthorized users without altering the underlying data. It’s ideal for columns like salaries, SSNs, or credit card numbers.

Real-World Example: Mask the Salary column for non-HR users.

Step 1: Add a Mask to the Column

ALTER TABLE Employees  
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');  

• Explanation:

  • FUNCTION = 'default()' replaces the actual value with ****.

  • Other masking functions include email(), random(), and partial().

Step 2: Grant Unmasking Permissions
Allow HR users to see the actual salaries.

GRANT UNMASK TO HR_Team;  

Result:

• Non-HR users see **** in the Salary column.

• HR users see the actual values.

3. Auditing and Compliance

Auditing tracks every change to your database, ensuring accountability and compliance with regulations like GDPR or HIPAA.

Real-World Example: Log all changes to the Employees table.

Step 1: Create a Server Audit
Define where audit logs will be stored (e.g., a file or the Windows Security Log).

CREATE SERVER AUDIT ComplianceAudit  
TO FILE (FILEPATH = 'C:\Audits\');  

• Explanation:

  • Logs are saved to C:\Audits\.

  • You can also use TO APPLICATION_LOG or TO SECURITY_LOG.

Step 2: Create a Database Audit Specification
Specify which actions to track (e.g., SELECT, INSERT, UPDATE, DELETE).

CREATE DATABASE AUDIT SPECIFICATION AuditSpec  
FOR SERVER AUDIT ComplianceAudit  
ADD (SELECT, INSERT, UPDATE, DELETE)  
WITH (STATE = ON);  

• Explanation:

  • Tracks all SELECT, INSERT, UPDATE, and DELETE operations.

  • WITH (STATE = ON) enables the audit immediately.

Step 3: View Audit Logs
Use SQL Server Management Studio (SSMS) or query the audit logs directly.

SELECT * FROM sys.fn_get_audit_file('C:\Audits\*', NULL, NULL);  

Result:

• Every change to the Employees table is logged to C:\Audits\.

• You can track who made changes, when, and what was changed.

Practice Tasks

Task 1: Implement Row-Level Security

1. Create a Sales table with columns SalesID, SalespersonID, and Revenue.

2. Use RLS to ensure salespeople can only view their own sales.

Hint:

CREATE FUNCTION dbo.fn_SalesFilter(@SalespersonID INT)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN SELECT 1 AS Access  
WHERE @SalespersonID = USER_ID();  

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE dbo.fn_SalesFilter(SalespersonID) ON dbo.Sales  
WITH (STATE = ON);  

Task 2: Apply Dynamic Data Masking

1. Add a CreditCardNumber column to the Customers table.

2. Mask the column so only finance users can see the full number.

Hint:

ALTER TABLE Customers  
ALTER COLUMN CreditCardNumber ADD MASKED WITH (FUNCTION = 'partial(4, "XXXX-XXXX-XXXX-", 4)');  

Task 3: Enable Auditing

1. Create a server audit that logs to C:\Audits\.

2. Track all changes to the Orders table.

Hint:

CREATE SERVER AUDIT OrderAudit  
TO FILE (FILEPATH = 'C:\Audits\');  

CREATE DATABASE AUDIT SPECIFICATION OrderAuditSpec  
FOR SERVER AUDIT OrderAudit  
ADD (SELECT, INSERT, UPDATE, DELETE)  
WITH (STATE = ON);  

Key Takeaways

• Row-Level Security (RLS): Restrict access to specific rows based on user roles.

• Dynamic Data Masking (DDM): Hide sensitive data from unauthorized users.

• Auditing: Track changes for compliance and accountability.

• Compliance: Use these tools to meet GDPR, HIPAA, and other regulations.