Skip to main content

Chapter 6: Database Security and Access Control

6.1 User Authentication and Authorization​

6.1.1 Authentication​

Authentication is the process of verifying the identity of a user, system, or entity. In database systems, this typically involves usernames and passwords, but can also include more advanced methods.

Types of Authentication:

  1. Password-based
  2. Multi-factor Authentication (MFA)
  3. Biometric Authentication
  4. Certificate-based Authentication

Example (SQL Server):

CREATE LOGIN JohnDoe WITH PASSWORD = 'StrongPassword123!';
CREATE USER JohnDoe FOR LOGIN JohnDoe;

6.1.2 Authorization​

Authorization determines what authenticated users are allowed to do within the database system.

Key Concepts:

  1. Principle of Least Privilege
  2. Role-Based Access Control (RBAC)
  3. Attribute-Based Access Control (ABAC)

Example (PostgreSQL):

CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO JohnDoe;

6.2 Encryption and Data Privacy​

6.2.1 Data Encryption​

Encryption converts data into a form that appears random to anyone without the decryption key.

Types of Encryption:

  1. Symmetric Encryption
  2. Asymmetric Encryption
  3. Hashing (for passwords)

Example (MySQL):

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    CreditCard VARBINARY(255)
);

INSERT INTO Customers (CustomerID, Name, CreditCard)
VALUES (1, 'John Doe', AES_ENCRYPT('1234-5678-9012-3456', 'encryption_key'));

6.2.2 Data Masking​

Data masking replaces sensitive data with realistic but false data.

Example (Oracle):

CREATE MASKING POLICY credit_card_mask
    FOR COLUMN Customers.CreditCard
    USING 'XXXX-XXXX-XXXX-' || SUBSTR(CreditCard, -4, 4);

6.3 Auditing and Compliance​

6.3.1 Database Auditing​

Auditing involves tracking and logging database activities to maintain an audit trail.

Key Aspects:

  1. Login/Logout Events
  2. Data Modification Events
  3. Schema Changes
  4. Privilege Changes

Example (SQL Server):

CREATE SERVER AUDIT DataAudit
TO FILE (FILEPATH = 'C:\Audits\')
WHERE object_name = 'Customers';

CREATE DATABASE AUDIT SPECIFICATION CustomerAudit
FOR SERVER AUDIT DataAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON Customers BY public);

6.3.2 Compliance​

Ensuring database systems comply with various regulations and standards:

  1. GDPR (General Data Protection Regulation)
  2. HIPAA (Health Insurance Portability and Accountability Act)
  3. PCI DSS (Payment Card Industry Data Security Standard)
  4. SOX (Sarbanes-Oxley Act)

6.4 Network Security for Databases​

6.4.1 Firewalls​

Firewalls control incoming and outgoing network traffic based on predetermined security rules.

Types:

  1. Network Firewalls
  2. Application Firewalls

6.4.2 Virtual Private Networks (VPNs)​

VPNs provide secure, encrypted connections over less secure networks.

6.4.3 Transport Layer Security (TLS)​

TLS encrypts data in transit between the client and the database server.

Example (PostgreSQL):

ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file = 'server.key';

6.5 Real-life Example: Implementing Security Measures for a Government Database​

Let's consider a scenario where we're tasked with implementing security measures for a government database containing sensitive citizen information.

Step 1: Authentication and Authorization​

  1. Implement Multi-Factor Authentication:
CREATE LOGIN GovEmployee WITH PASSWORD = 'StrongPass123!' 
    MUST_CHANGE, CHECK_EXPIRATION = ON;
-- Additional step: Set up MFA using a third-party solution
  1. Set up Role-Based Access Control:
CREATE ROLE DataEntry;
CREATE ROLE DataAnalyst;
CREATE ROLE Administrator;

GRANT INSERT, UPDATE ON CitizenData TO DataEntry;
GRANT SELECT ON CitizenData TO DataAnalyst;
GRANT ALL PRIVILEGES ON CitizenData TO Administrator;

-- Assign roles to users
EXEC sp_addrolemember 'DataEntry', 'JohnDoe';
EXEC sp_addrolemember 'DataAnalyst', 'JaneSmith';
EXEC sp_addrolemember 'Administrator', 'AdminUser';

Step 2: Data Encryption​

  1. Implement Transparent Data Encryption (TDE):
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ComplexMasterKey123!';

CREATE CERTIFICATE TDECert 
    WITH SUBJECT = 'TDE Certificate';

CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDECert;

ALTER DATABASE GovDatabase
    SET ENCRYPTION ON;
  1. Encrypt Sensitive Columns:
ALTER TABLE CitizenData
    ALTER COLUMN SocialSecurityNumber varbinary(256) ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK_Auto1);

Step 3: Auditing​

Set up Comprehensive Auditing:

CREATE SERVER AUDIT GovDatabaseAudit
TO FILE (FILEPATH = 'C:\Audits\GovDatabase\')
WHERE object_name = 'CitizenData';

CREATE DATABASE AUDIT SPECIFICATION CitizenDataAudit
FOR SERVER AUDIT GovDatabaseAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON CitizenData BY public),
ADD (FAILED_LOGIN_GROUP);

Step 4: Network Security​

  1. Configure Firewall Rules:

    • Allow connections only from specific IP ranges
    • Restrict database port access

  2. Set up TLS for Encrypted Connections:

ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file = 'server.key';

Step 5: Regular Security Audits and Updates​

  1. Schedule regular penetration testing
  2. Keep the database system and all security patches up to date
  3. Regularly review and update access permissions

By implementing these measures, we create a robust security framework for the government database. This approach addresses authentication, authorization, encryption, auditing, and network security, providing a comprehensive defense against various security threats.