Database Security

1-- Windows Authentication dùng Active Directory
2-- More secure: No passwords stored in SQL Server
3-- Single sign-on: Use Windows credentials
4
5-- Create login from Windows user/group
6CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS;
7CREATE LOGIN [DOMAIN\DBAGroup] FROM WINDOWS;
8
9-- Check current authentication mode
10SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly;
11-- 1 = Windows only, 0 = Mixed mode

1-- SQL Auth: Username/password stored in SQL Server
2-- Required for non-Windows clients
3
4-- Create SQL login
5CREATE LOGIN AppUser 
6WITH PASSWORD = 'StrongP@ssw0rd!',
7     DEFAULT_DATABASE = AdventureWorks2019,
8     CHECK_POLICY = ON,          -- Enforce Windows password policy
9     CHECK_EXPIRATION = ON;      -- Password expires
10
11-- Change password
12ALTER LOGIN AppUser WITH PASSWORD = 'NewStrongP@ssw0rd!';
13
14-- Disable/Enable login
15ALTER LOGIN AppUser DISABLE;
16ALTER LOGIN AppUser ENABLE;
17
18-- Lock account after failed attempts (via policy)
19ALTER LOGIN AppUser WITH CHECK_POLICY = ON;

1-- 1. Create Server Login
2CREATE LOGIN AppLogin WITH PASSWORD = 'P@ssw0rd123!';
3
4-- 2. Create Database User mapped to Login
5USE AdventureWorks2019;
6CREATE USER AppUser FOR LOGIN AppLogin;
7
8-- Or create without login (contained database)
9CREATE USER ContainedUser WITH PASSWORD = 'P@ssw0rd123!';

1-- Add login to server role
2ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\DBAUser];
3ALTER SERVER ROLE dbcreator ADD MEMBER DevLead;
4
5-- Check server role membership
6SELECT 
7    sp.name AS LoginName,
8    sp.type_desc,
9    sr.name AS ServerRole
10FROM sys.server_principals sp
11JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
12JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id;

1-- Add user to database role
2ALTER ROLE db_datareader ADD MEMBER AppUser;
3ALTER ROLE db_datawriter ADD MEMBER AppUser;
4
5-- Check database role membership
6SELECT 
7    dp.name AS UserName,
8    dp.type_desc,
9    r.name AS RoleName
10FROM sys.database_principals dp
11JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
12JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id;

1-- Create custom role
2CREATE ROLE SalesReporting;
3
4-- Grant permissions to role
5GRANT SELECT ON SCHEMA::Sales TO SalesReporting;
6GRANT EXECUTE ON Sales.usp_GetOrders TO SalesReporting;
7
8-- Add users to custom role
9ALTER ROLE SalesReporting ADD MEMBER ReportUser;
10ALTER ROLE SalesReporting ADD MEMBER AnalystUser;
11
12-- Remove user from role
13ALTER ROLE SalesReporting DROP MEMBER OldUser;
14
15-- Drop role
16DROP ROLE SalesReporting;

1-- GRANT: Give permission
2GRANT SELECT ON Sales.Customer TO AppUser;
3GRANT INSERT, UPDATE ON Sales.[Order] TO AppUser;
4GRANT EXECUTE ON Sales.usp_CreateOrder TO AppUser;
5
6-- DENY: Explicitly block (overrides GRANT)
7DENY DELETE ON Sales.Customer TO AppUser;
8
9-- REVOKE: Remove permission (neither GRANT nor DENY)
10REVOKE SELECT ON Sales.Customer FROM AppUser;
11
12-- Permission hierarchy
13-- DENY > GRANT > REVOKE (no permission)

1-- Grant access to all objects in schema
2GRANT SELECT ON SCHEMA::Sales TO ReportingRole;
3GRANT EXECUTE ON SCHEMA::Sales TO AppRole;
4
5-- More specific
6GRANT SELECT, INSERT, UPDATE ON SCHEMA::Sales TO AppRole;
7DENY DELETE ON SCHEMA::Sales TO AppRole;

1-- Table permissions
2GRANT SELECT (CustomerID, FirstName, LastName) ON Customer.Customer TO LimitedUser;
3-- Only specific columns!
4
5-- View permissions
6GRANT SELECT ON Sales.vw_OrderSummary TO ReportingUser;
7
8-- Stored procedure permissions
9GRANT EXECUTE ON Sales.usp_GetOrders TO AppUser;
10
11-- With GRANT option (user can grant to others)
12GRANT SELECT ON Sales.Customer TO TeamLead WITH GRANT OPTION;

1-- Check user's permissions
2SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
3SELECT * FROM fn_my_permissions('Sales.Customer', 'OBJECT');
4
5-- Check specific user's permissions
6EXECUTE AS USER = 'AppUser';
7SELECT * FROM fn_my_permissions('Sales.Customer', 'OBJECT');
8REVERT;
9
10-- All permissions for a user
11SELECT 
12    dp.name AS UserName,
13    o.name AS ObjectName,
14    p.permission_name,
15    p.state_desc
16FROM sys.database_permissions p
17JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
18LEFT JOIN sys.objects o ON p.major_id = o.object_id
19WHERE dp.name = 'AppUser';

1-- Create security predicate function
2CREATE FUNCTION Security.fn_CustomerFilter(@CustomerID INT)
3RETURNS TABLE
4WITH SCHEMABINDING
5AS
6RETURN 
7    SELECT 1 AS Result
8    WHERE @CustomerID = (
9        SELECT CustomerID 
10        FROM Customer.Customer 
11        WHERE Email = USER_NAME()
12    )
13    OR USER_NAME() = 'dbo';  -- Admin sees all
14GO
15
16-- Create security policy
17CREATE SECURITY POLICY CustomerPolicy
18ADD FILTER PREDICATE Security.fn_CustomerFilter(CustomerID) 
19ON Sales.[Order]
20WITH (STATE = ON);
21
22-- Now each user only sees their own orders
23-- User 'customer1@email.com' sees only their orders

1-- TDE encrypts data at rest (on disk)
2-- Automatic, transparent to applications
3
4-- 1. Create master key
5USE master;
6CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyP@ssw0rd!';
7
8-- 2. Create certificate
9CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
10
11-- 3. Create database encryption key
12USE AdventureWorks2019;
13CREATE DATABASE ENCRYPTION KEY
14WITH ALGORITHM = AES_256
15ENCRYPTION BY SERVER CERTIFICATE TDECert;
16
17-- 4. Enable encryption
18ALTER DATABASE AdventureWorks2019 SET ENCRYPTION ON;
19
20-- Check encryption status
21SELECT 
22    db.name,
23    db.is_encrypted,
24    dek.encryption_state,
25    dek.percent_complete
26FROM sys.databases db
27LEFT JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id;

1-- Encrypt specific columns
2
3-- 1. Create symmetric key
4CREATE SYMMETRIC KEY SSN_Key
5WITH ALGORITHM = AES_256
6ENCRYPTION BY PASSWORD = 'SymKeyP@ssw0rd!';
7
8-- 2. Create table with encrypted column
9CREATE TABLE HR.EmployeeSensitive (
10    EmployeeID INT PRIMARY KEY,
11    SSN VARBINARY(256),  -- Encrypted data stored as binary
12    Salary VARBINARY(256)
13);
14
15-- 3. Insert encrypted data
16OPEN SYMMETRIC KEY SSN_Key DECRYPTION BY PASSWORD = 'SymKeyP@ssw0rd!';
17
18INSERT INTO HR.EmployeeSensitive (EmployeeID, SSN, Salary)
19VALUES (
20    1,
21    EncryptByKey(Key_GUID('SSN_Key'), '123-45-6789'),
22    EncryptByKey(Key_GUID('SSN_Key'), '75000')
23);
24
25CLOSE SYMMETRIC KEY SSN_Key;
26
27-- 4. Read decrypted data
28OPEN SYMMETRIC KEY SSN_Key DECRYPTION BY PASSWORD = 'SymKeyP@ssw0rd!';
29
30SELECT 
31    EmployeeID,
32    CONVERT(VARCHAR(20), DecryptByKey(SSN)) AS SSN,
33    CONVERT(VARCHAR(20), DecryptByKey(Salary)) AS Salary
34FROM HR.EmployeeSensitive;
35
36CLOSE SYMMETRIC KEY SSN_Key;

1-- Always Encrypted: Keys stored outside SQL Server
2-- Data encrypted/decrypted by client application
3-- SQL Server never sees plaintext
4
5-- Create column master key (references key in Windows Certificate Store or Azure Key Vault)
6CREATE COLUMN MASTER KEY CMK_Auto
7WITH (
8    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
9    KEY_PATH = 'CurrentUser/My/Certificate_Thumbprint'
10);
11
12-- Create column encryption key
13CREATE COLUMN ENCRYPTION KEY CEK_Auto
14WITH VALUES (
15    COLUMN_MASTER_KEY = CMK_Auto,
16    ALGORITHM = 'RSA_OAEP',
17    ENCRYPTED_VALUE = 0x...
18);
19
20-- Create table with encrypted columns
21CREATE TABLE HR.EmployeeSecure (
22    EmployeeID INT PRIMARY KEY,
23    SSN VARCHAR(11) ENCRYPTED WITH (
24        COLUMN_ENCRYPTION_KEY = CEK_Auto,
25        ENCRYPTION_TYPE = DETERMINISTIC,  -- Can be used in WHERE
26        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
27    ),
28    Salary INT ENCRYPTED WITH (
29        COLUMN_ENCRYPTION_KEY = CEK_Auto,
30        ENCRYPTION_TYPE = RANDOMIZED,  -- More secure, cannot search
31        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
32    )
33);

1-- Vulnerable code (DO NOT DO THIS!)
2DECLARE @sql NVARCHAR(500);
3SET @sql = 'SELECT * FROM Users WHERE Username = ''' + @UserInput + '''';
4EXEC(@sql);
5
6-- If @UserInput = "admin'; DROP TABLE Users; --"
7-- Becomes: SELECT * FROM Users WHERE Username = 'admin'; DROP TABLE Users; --'
8-- TABLE DROPPED!

1-- 1. Use Parameterized Queries / Stored Procedures
2CREATE PROCEDURE usp_GetUser
3    @Username NVARCHAR(50)
4AS
5BEGIN
6    SELECT * FROM Users WHERE Username = @Username;
7    -- Parameter is treated as data, not code!
8END;
9
10-- 2. If dynamic SQL is necessary, use sp_executesql with parameters
11DECLARE @sql NVARCHAR(500);
12SET @sql = N'SELECT * FROM Users WHERE Username = @User';
13
14EXEC sp_executesql @sql, 
15    N'@User NVARCHAR(50)', 
16    @User = @UserInput;  -- Parameterized!
17
18-- 3. Use QUOTENAME for identifiers
19DECLARE @TableName NVARCHAR(128) = 'Users';
20SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName);
21-- QUOTENAME escapes special characters
22
23-- 4. Validate and whitelist inputs
24IF @TableName NOT IN ('Users', 'Products', 'Orders')
25BEGIN
26    RAISERROR('Invalid table name', 16, 1);
27    RETURN;
28END

1-- Create server audit
2CREATE SERVER AUDIT SecurityAudit
3TO FILE (FILEPATH = 'C:\SQLAudit\', MAXSIZE = 100MB)
4WITH (ON_FAILURE = CONTINUE);
5
6ALTER SERVER AUDIT SecurityAudit WITH (STATE = ON);
7
8-- Create database audit specification
9USE AdventureWorks2019;
10
11CREATE DATABASE AUDIT SPECIFICATION SalesAudit
12FOR SERVER AUDIT SecurityAudit
13ADD (SELECT, INSERT, UPDATE, DELETE ON Sales.[Order] BY public),
14ADD (EXECUTE ON SCHEMA::Sales BY public)
15WITH (STATE = ON);
16
17-- View audit logs
18SELECT 
19    event_time,
20    action_id,
21    succeeded,
22    session_server_principal_name,
23    database_name,
24    schema_name,
25    object_name,
26    statement
27FROM sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', NULL, NULL);

1-- Enable CDC for database
2EXEC sys.sp_cdc_enable_db;
3
4-- Enable CDC for table
5EXEC sys.sp_cdc_enable_table
6    @source_schema = 'Sales',
7    @source_name = 'Order',
8    @role_name = 'cdc_Admin',
9    @capture_instance = 'Sales_Order',
10    @supports_net_changes = 1;
11
12-- Query changes
13SELECT * FROM cdc.fn_cdc_get_all_changes_Sales_Order(
14    @from_lsn, @to_lsn, 'all'
15);

1-- ❌ Bad: Give full access
2ALTER ROLE db_owner ADD MEMBER AppUser;
3
4-- ✅ Good: Only necessary permissions
5CREATE ROLE AppRole;
6GRANT SELECT ON SCHEMA::Sales TO AppRole;
7GRANT INSERT, UPDATE ON Sales.[Order] TO AppRole;
8GRANT EXECUTE ON Sales.usp_CreateOrder TO AppRole;
9ALTER ROLE AppRole ADD MEMBER AppUser;

1-- Example: Setup roles for AdventureWorks application
2
3-- 1. Create roles
4CREATE ROLE SalesRole;
5CREATE ROLE HRRole;
6CREATE ROLE ReportingRole;
7
8-- 2. Sales permissions
9GRANT SELECT, INSERT, UPDATE ON SCHEMA::Sales TO SalesRole;
10GRANT SELECT ON SCHEMA::Production TO SalesRole;
11GRANT EXECUTE ON SCHEMA::Sales TO SalesRole;
12DENY DELETE ON SCHEMA::Sales TO SalesRole;
13
14-- 3. HR permissions
15GRANT SELECT, INSERT, UPDATE ON SCHEMA::HumanResources TO HRRole;
16GRANT SELECT ON SCHEMA::Person TO HRRole;
17DENY SELECT ON HumanResources.EmployeePayHistory TO SalesRole;
18
19-- 4. Reporting permissions (read-only)
20GRANT SELECT ON SCHEMA::Sales TO ReportingRole;
21GRANT SELECT ON SCHEMA::Production TO ReportingRole;
22GRANT SELECT ON SCHEMA::Person TO ReportingRole;
23
24-- 5. Create users and assign roles
25CREATE USER SalesApp FOR LOGIN SalesAppLogin;
26CREATE USER HRApp FOR LOGIN HRAppLogin;
27CREATE USER ReportService FOR LOGIN ReportServiceLogin;
28
29ALTER ROLE SalesRole ADD MEMBER SalesApp;
30ALTER ROLE HRRole ADD MEMBER HRApp;
31ALTER ROLE ReportingRole ADD MEMBER ReportService;