Bảo mật SQL Server

Bảo mật SQL Server là lớp phòng thủ quan trọng trong kiến trúc ứng dụng. Một database không được bảo mật đúng cách có thể gây rò rỉ dữ liệu, vi phạm compliance, và thiệt hại nghiêm trọng cho doanh nghiệp. Chủ đề này bao gồm từ authentication cơ bản đến encryption nâng cao.

1. Authentication Modes

Windows Authentication (Integrated Security)

Xác thực qua Active Directory/Windows Security. SQL Server tin tưởng Windows đã xác thực user — không cần lưu password trong SQL Server.

Connection String: 
Server=MyServer;Database=MyDB;Integrated Security=True;
                                                     ↑
                              Dùng Windows credentials của process đang chạy

Ưu điểm:

• Không lưu password trong application config
• Hỗ trợ Kerberos/NTLM, MFA qua Azure AD
• Tự động expired/rotate theo Group Policy
• Single Sign-On với Windows ecosystem

SQL Server Authentication

Username/Password được lưu trong SQL Server master database.

Connection String:
Server=MyServer;Database=MyDB;User Id=AppLogin;Password=Str0ng!Pass;

Khi nào cần: Non-Windows clients (Linux, Mac, containers), cross-domain scenarios, legacy applications.

Rủi ro: Password có thể bị lộ trong config files, logs, memory dumps.

Mixed Mode (cả hai)

Hỗ trợ cả Windows và SQL Server Authentication. Bật khi:

• Có legacy apps dùng SQL Auth
• Cần sa account (đặc biệt khi setup ban đầu)

-- Kiểm tra authentication mode
SELECT
    CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
        WHEN 1 THEN 'Windows Authentication Only'
        ELSE 'Mixed Mode (Windows + SQL)'
    END AS AuthenticationMode;

-- Đổi sang Mixed Mode (cần restart SQL Server)
-- GUI: SSMS → Server Properties → Security → Server Authentication
-- Registry: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer
-- LoginMode: 1 = Windows, 2 = Mixed

Tài khoản sa (System Administrator)

-- sa là built-in SQL Server login với sysadmin role
-- Best practices:
-- 1. Đổi tên sa (security through obscurity)
ALTER LOGIN sa WITH NAME = [SqlAdminAccount];

-- 2. Disable sa nếu không dùng
ALTER LOGIN sa DISABLE;

-- 3. Nếu cần enable, đặt strong password
ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = 'V3ry$tr0ng!RandomP@ss2024#';

2. Principals — Logins, Users, Roles

Hierarchy của Principals

SQL Server Instance Level:
├── Windows Login (DOMAIN\User, DOMAIN\Group)
├── SQL Server Login (username/password)
└── Server Roles (sysadmin, securityadmin, ...)

Database Level (per database):
├── Database User (mapped to Login)
├── Database Roles (db_owner, db_datareader, ...)
└── Application Roles (activate with password)

Tạo và Quản lý Logins

-- Tạo Windows Login
CREATE LOGIN [DOMAIN\HuyNgo] FROM WINDOWS WITH DEFAULT_DATABASE = MyDB;
CREATE LOGIN [DOMAIN\AppServiceGroup] FROM WINDOWS; -- AD Group

-- Tạo SQL Server Login
CREATE LOGIN AppLogin 
WITH PASSWORD = 'Str0ng!P@ssw0rd#2024',
     DEFAULT_DATABASE = AppDB,
     CHECK_EXPIRATION = ON,  -- Enforce password expiration  
     CHECK_POLICY = ON;      -- Enforce Windows password policy

-- Kiểm tra logins
SELECT 
    name, 
    type_desc, 
    is_disabled,
    create_date,
    password_hash IS NOT NULL AS has_password
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G') -- S=SQL Login, U=Windows User, G=Windows Group
ORDER BY type, name;

Tạo và Quản lý Database Users

USE AppDB;

-- Tạo User từ Login
CREATE USER HuyNgo FOR LOGIN [DOMAIN\HuyNgo];
CREATE USER AppUser FOR LOGIN AppLogin;

-- Tạo User không có Login (contained database)
CREATE USER ContainedUser WITH PASSWORD = 'Pass!word';

-- Tạo User cho certificate/asymmetric key
CREATE USER CertUser FOR CERTIFICATE MyCert;

-- Kiểm tra users trong database
SELECT 
    u.name AS UserName,
    u.type_desc,
    l.name AS MappedLogin,
    u.default_schema_name
FROM sys.database_principals u
LEFT JOIN sys.server_principals l ON u.sid = l.sid
WHERE u.type NOT IN ('R') -- Loại bỏ roles
ORDER BY u.name;

-- Orphaned users (user không có Login tương ứng)
EXEC sp_change_users_login 'Report';
-- Fix orphaned users:
EXEC sp_change_users_login 'UPDATE_ONE', 'OrphanedUser', 'MatchingLogin';

3. Server Roles

Fixed Server Roles không thể sửa đổi (ngoại trừ user-defined server roles từ SQL 2012+):

-- Thêm Login vào Server Role
ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\DBAdmin];
ALTER SERVER ROLE dbcreator ADD MEMBER DevLogin;

-- Xem Server Role members
SELECT 
    r.name AS role_name,
    m.name AS member_name,
    m.type_desc
FROM sys.server_role_members srm
JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

-- User-defined Server Role (SQL 2012+)
CREATE SERVER ROLE ReadonlyDBA;
GRANT VIEW SERVER STATE TO ReadonlyDBA;
GRANT VIEW ANY DATABASE TO ReadonlyDBA;
ALTER SERVER ROLE ReadonlyDBA ADD MEMBER MonitorLogin;

4. Database Roles

Fixed Database Roles

-- Thêm User vào Database Role
ALTER ROLE db_datareader ADD MEMBER AppUser;
ALTER ROLE db_datawriter ADD MEMBER AppUser;

-- Tạo Custom Database Role (Principle of Least Privilege)
CREATE ROLE OrderManagers;
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO OrderManagers;
GRANT SELECT, INSERT ON dbo.OrderDetails TO OrderManagers;
GRANT EXECUTE ON dbo.CreateOrder TO OrderManagers;
GRANT EXECUTE ON dbo.UpdateOrderStatus TO OrderManagers;

ALTER ROLE OrderManagers ADD MEMBER UserA;
ALTER ROLE OrderManagers ADD MEMBER UserB;

-- Xem Role members
SELECT 
    r.name AS role_name,
    m.name AS member_name
FROM sys.database_role_members drm
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

5. Object Permissions — GRANT, DENY, REVOKE

Cú pháp cơ bản

-- GRANT permissions
GRANT SELECT ON dbo.Products TO AppUser;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Orders TO AppUser;
GRANT EXECUTE ON dbo.GetOrderDetails TO AppUser;
GRANT ALTER ON SCHEMA::dbo TO PowerUser;

-- GRANT WITH GRANT OPTION (cho phép user gán quyền cho người khác)
GRANT SELECT ON dbo.Reports TO ManagerUser WITH GRANT OPTION;

-- DENY (override mọi GRANT)
DENY DELETE ON dbo.Orders TO AppUser;         -- Không thể DELETE
DENY SELECT ON dbo.SalaryTable TO AllStaff;   -- Không ai đọc được

-- REVOKE (xóa GRANT hoặc DENY)
REVOKE SELECT ON dbo.Products FROM AppUser;
REVOKE DELETE ON dbo.Orders FROM AppUser; -- Xóa DENY, nhưng vẫn không có GRANT

Kiểm tra Permissions

-- Permissions của một user
SELECT 
    dp.permission_name,
    dp.state_desc,     -- GRANT, DENY, REVOKE
    dp.class_desc,
    OBJECT_NAME(dp.major_id) AS object_name,
    sp.name AS principal_name
FROM sys.database_permissions dp
JOIN sys.database_principals sp ON dp.grantee_principal_id = sp.principal_id
WHERE sp.name = 'AppUser'
ORDER BY dp.class_desc, dp.permission_name;

-- Effective permissions của current user
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');

-- HAS_PERMS_BY_NAME để kiểm tra trong code
IF HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'SELECT') = 1
    PRINT 'Can SELECT from Orders';

6. Schema-based Permissions

Schema là namespace chứa objects. GRANT quyền trên schema = tự động có quyền trên tất cả objects hiện tại và tương lai trong schema đó.

-- Tạo schemas theo nhóm chức năng
CREATE SCHEMA Sales AUTHORIZATION dbo;
CREATE SCHEMA HR AUTHORIZATION dbo;
CREATE SCHEMA Finance AUTHORIZATION dbo;
CREATE SCHEMA Reporting AUTHORIZATION dbo;

-- Objects trong schemas
CREATE TABLE Sales.Orders (...);
CREATE TABLE Sales.Customers (...);
CREATE TABLE HR.Employees (...);
CREATE TABLE Finance.Payroll (...);

-- Permission model đơn giản, dễ quản lý:
-- App login chỉ cần Sales schema
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO SalesAppUser;
GRANT EXECUTE ON SCHEMA::Sales TO SalesAppUser;

-- Report login chỉ đọc Reporting schema
GRANT SELECT ON SCHEMA::Reporting TO ReportUser;

-- HR admin
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::HR TO HRAppUser;
DENY SELECT ON HR.Employees TO FinanceUser; -- Tường minh deny

-- Xem tất cả schemas và owners
SELECT name, principal_id, SCHEMA_NAME(schema_id) 
FROM sys.schemas
ORDER BY name;

7. Row-Level Security (RLS)

RLS cho phép kiểm soát access ở cấp row dựa trên security predicate function.

Filter Predicate (Chỉ thấy rows được phép)

-- Ví dụ: Sales rep chỉ thấy orders được assign cho họ
CREATE TABLE Sales.Orders (
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    SalesRepLogin NVARCHAR(100),
    Amount DECIMAL(10,2),
    OrderDate DATE
);

-- Security predicate function
CREATE FUNCTION Security.fn_OrderAccess(@SalesRepLogin NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
    SELECT 1 AS allowed
    WHERE 
        @SalesRepLogin = USER_NAME()      -- Chính user đó
        OR IS_ROLEMEMBER('db_owner') = 1  -- Hoặc admin
        OR IS_ROLEMEMBER('SalesManager') = 1; -- Hoặc manager

-- Tạo Security Policy
CREATE SECURITY POLICY OrderFilterPolicy
ADD FILTER PREDICATE Security.fn_OrderAccess(SalesRepLogin) 
    ON Sales.Orders
WITH (STATE = ON, SCHEMABINDING = ON);

-- Test:
EXECUTE AS USER = 'rep_nguyen';
SELECT * FROM Sales.Orders; -- Chỉ thấy orders của rep_nguyen
REVERT;

Block Predicate (Ngăn INSERT/UPDATE vi phạm policy)

-- Block predicate: Ngăn insert row với SalesRepLogin khác với current user
CREATE SECURITY POLICY OrderFilterPolicy
ADD FILTER PREDICATE Security.fn_OrderAccess(SalesRepLogin) ON Sales.Orders,
ADD BLOCK PREDICATE Security.fn_OrderAccess(SalesRepLogin) ON Sales.Orders 
    AFTER INSERT, -- Ngăn insert rows không thuộc về họ
    AFTER UPDATE  -- Ngăn update để chuyển ownership
WITH (STATE = ON);

-- Không cần modify queries trong application!
-- Tất cả SELECT/INSERT/UPDATE tự động được filter

RLS với Session Context

-- Dùng Session Context cho multi-tenant (tốt hơn USER_NAME() khi app dùng connection pool)
CREATE FUNCTION dbo.fn_TenantFilter(@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
    SELECT 1 AS OK
    WHERE @TenantId = CONVERT(INT, SESSION_CONTEXT(N'TenantId'))
        OR IS_MEMBER('db_owner') = 1;

CREATE SECURITY POLICY TenantIsolationPolicy
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Orders,
ADD BLOCK PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Orders
WITH (STATE = ON);

-- Application set context trước khi query (sau login):
EXEC sys.sp_set_session_context 
    @key = N'TenantId', 
    @value = 42,
    @read_only = 1; -- Ngăn application tự thay đổi TenantId!

8. Dynamic Data Masking (DDM)

DDM che giấu dữ liệu nhạy cảm khi query — không thay đổi dữ liệu thực trong storage.

Masking Functions

CREATE TABLE dbo.CustomerPII (
    CustomerId    INT PRIMARY KEY,
    FullName      NVARCHAR(100), -- Không mask
    
    -- Email: hiXX@XXXX.com
    Email         NVARCHAR(200) MASKED WITH (FUNCTION = 'email()'),
    
    -- Che giấu hoàn toàn: xxxx
    Password      NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
    
    -- Chỉ hiện 2 ký tự đầu và 2 cuối: "Hu...go"
    NickName      NVARCHAR(50)  MASKED WITH (FUNCTION = 'partial(2,"...",2)'),
    
    -- Số điện thoại: XXX-XXX-1234
    Phone         NVARCHAR(20)  MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)'),
    
    -- Credit card: XXXX-XXXX-XXXX-5678
    CreditCard    NVARCHAR(20)  MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)'),
    
    -- Random number trong khoảng
    FakeIncome    DECIMAL(10,2) MASKED WITH (FUNCTION = 'random(1000, 9999)'),
    
    -- Date: 01.01.1900
    BirthDate     DATE          MASKED WITH (FUNCTION = 'default()')
);

-- Thêm mask vào cột đã có:
ALTER TABLE dbo.CustomerPII
ALTER COLUMN SSN NVARCHAR(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');

-- Bỏ mask:
ALTER TABLE dbo.CustomerPII ALTER COLUMN Email DROP MASKED;

-- UNMASK permission:
GRANT UNMASK TO PowerUserRole;           -- Toàn bộ database
GRANT UNMASK ON dbo.CustomerPII TO AdminUser; -- Chỉ table đó

-- Test:
EXECUTE AS USER = 'NormalUser';
SELECT CustomerId, FullName, Email, Phone, CreditCard FROM dbo.CustomerPII;
-- Email: aXX@XXXX.com, Phone: XXX-XXX-5678, CreditCard: XXXX-XXXX-XXXX-9012
REVERT;

EXECUTE AS USER = 'AdminUser';
SELECT CustomerId, FullName, Email, Phone, CreditCard FROM dbo.CustomerPII;
-- Thấy dữ liệu thật
REVERT;

9. Always Encrypted

Always Encrypted đảm bảo chỉ client application mới có thể đọc dữ liệu nhạy cảm — SQL Server, DBA, Cloud admin đều không đọc được. Encryption/Decryption xảy ra hoàn toàn ở phía client.

Kiến trúc

Client Application
  ├── Column Master Key (CMK): Lưu trong Key Vault / Windows Cert Store
  └── Column Encryption Key (CEK): Được encrypt bởi CMK, lưu trong SQL Server

SQL Server
  └── Chỉ thấy ciphertext (bytes) — không có CMK, không decrypt được

Setup Always Encrypted

-- Step 1: Tạo Column Master Key metadata (key thực lưu ở Key Vault/cert store)
CREATE COLUMN MASTER KEY MyCMK
WITH (
    KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
    KEY_PATH = 'https://mykeyvault.vault.azure.net/keys/MyCMK/abc123'
);
-- Hoặc Windows Certificate Store:
CREATE COLUMN MASTER KEY MyCMK
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'LocalMachine/My/ABC123DEF456...'
);

-- Step 2: Tạo Column Encryption Key (encrypted bằng CMK)
CREATE COLUMN ENCRYPTION KEY MyCEK
WITH VALUES (
    COLUMN_MASTER_KEY = MyCMK,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x01234567... -- Giá trị encrypted
);

-- Step 3: Tạo table với encrypted columns
CREATE TABLE dbo.Patients (
    PatientId    INT PRIMARY KEY,
    Name         NVARCHAR(100), -- Không encrypt
    SSN          CHAR(11) ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = Deterministic, -- Cho phép equality search
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ),
    Diagnosis    NVARCHAR(500) ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = Randomized, -- Bảo mật hơn, không search được
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    )
);

Deterministic vs Randomized Encryption

10. Transparent Data Encryption (TDE)

TDE mã hóa toàn bộ database files (data, log, backup) ở tầng storage. Hoàn toàn transparent với application.

Setup TDE

-- Step 1: Tạo Database Master Key trong master database
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng#MasterKey!Pass';

-- Step 2: Tạo Certificate để bảo vệ TDE key
CREATE CERTIFICATE TDECert 
WITH SUBJECT = 'TDE Certificate for AppDB',
     EXPIRY_DATE = '2099-12-31';

-- ⚠️ QUAN TRỌNG: Backup certificate ngay! Nếu mất certificate, mất dữ liệu!
BACKUP CERTIFICATE TDECert 
TO FILE = 'C:\Backups\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backups\TDECert_Key.pvk',
    ENCRYPTION BY PASSWORD = 'BackupKeyPassword!2024'
);

-- Step 3: Tạo Database Encryption Key trong database cần encrypt
USE AppDB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

-- Step 4: Bật TDE
ALTER DATABASE AppDB SET ENCRYPTION ON;

-- Monitor tiến độ encryption
SELECT 
    DB_NAME(database_id) AS db_name,
    encryption_state,
    CASE encryption_state
        WHEN 0 THEN 'No encryption'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
        WHEN 5 THEN 'Decryption in progress'
        WHEN 6 THEN 'Protection change in progress'
    END AS encryption_state_desc,
    percent_complete,
    encryptor_thumbprint
FROM sys.dm_database_encryption_keys;

Khôi phục TDE Database sang server khác

-- Trên server đích: Phải restore certificate TRƯỚC khi restore database
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewServerMasterKeyPass';

CREATE CERTIFICATE TDECert
FROM FILE = 'C:\Backups\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backups\TDECert_Key.pvk',
    DECRYPTION BY PASSWORD = 'BackupKeyPassword!2024'
);

-- Bây giờ mới có thể restore database
RESTORE DATABASE AppDB FROM DISK = '...';

11. SQL Server Audit

Audit tracks các hành động trong SQL Server và ghi vào file, Windows Event Log, hoặc Application Log.

Server Audit và Audit Specification

-- Bước 1: Tạo Server Audit (cấu hình destination)
CREATE SERVER AUDIT ProductionAudit
TO FILE (
    FILEPATH = 'D:\SQLAudit\',
    MAXSIZE = 100 MB,
    MAX_FILES = 50,             -- Giữ 50 files
    RESERVE_DISK_SPACE = OFF
)
WITH (
    QUEUE_DELAY = 1000,         -- ms, 0 = synchronous
    ON_FAILURE = CONTINUE,      -- CONTINUE hoặc SHUTDOWN hoặc FAIL_OPERATION
    AUDIT_GUID = NEWID()
);

ALTER SERVER AUDIT ProductionAudit WITH (STATE = ON);

-- Bước 2a: Server Audit Specification (server-level events)
CREATE SERVER AUDIT SPECIFICATION ServerSecurityAudit
FOR SERVER AUDIT ProductionAudit
    ADD (FAILED_LOGIN_GROUP),               -- Đăng nhập thất bại
    ADD (SUCCESSFUL_LOGIN_GROUP),           -- Đăng nhập thành công
    ADD (LOGOUT_GROUP),                     -- Đăng xuất
    ADD (LOGIN_CHANGE_PASSWORD_GROUP),      -- Đổi password
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),  -- Thay đổi server role
    ADD (CREATE_LOGIN_GROUP),               -- Tạo login
    ADD (ALTER_LOGIN_GROUP),                -- Sửa login
    ADD (DROP_LOGIN_GROUP)                  -- Xóa login
WITH (STATE = ON);

-- Bước 2b: Database Audit Specification (database-level events)
USE SensitiveDatabase;
CREATE DATABASE AUDIT SPECIFICATION DataChangeAudit
FOR SERVER AUDIT ProductionAudit
    ADD (SELECT ON dbo.Payroll BY public),         -- Ai đọc Payroll
    ADD (INSERT, UPDATE, DELETE ON dbo.Orders BY public), -- Thay đổi Orders
    ADD (EXECUTE ON SCHEMA::dbo BY public),        -- Gọi stored procedures
    ADD (DATABASE_OBJECT_CHANGE_GROUP),            -- DDL changes
    ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)    -- Permission changes
WITH (STATE = ON);

Xem Audit Logs

-- Đọc từ file
SELECT TOP 100
    event_time AT TIME ZONE 'SE Asia Standard Time' AS event_time_local,
    action_id,
    succeeded,
    session_server_principal_name AS login_name,
    server_instance_name,
    database_name,
    schema_name,
    object_name,
    statement,
    additional_information
FROM sys.fn_get_audit_file(
    'D:\SQLAudit\ProductionAudit_*.sqlaudit',
    DEFAULT,
    DEFAULT
)
WHERE action_id IN ('SL', 'IN', 'UP', 'DL') -- SELECT, INSERT, UPDATE, DELETE
ORDER BY event_time DESC;

-- Filter theo user
SELECT *
FROM sys.fn_get_audit_file('D:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE session_server_principal_name = 'SuspiciousUser'
ORDER BY event_time;

12. EXECUTE AS — Impersonation

-- Impersonation tạm thời trong session
EXECUTE AS USER = 'LimitedUser';
    SELECT * FROM dbo.Orders; -- Thực thi với quyền LimitedUser
    -- Nếu LimitedUser không có quyền → Error
REVERT; -- Quay lại context gốc (bắt buộc!)

-- EXECUTE AS trong Stored Procedure
CREATE PROCEDURE dbo.GetRestrictedData
WITH EXECUTE AS OWNER  -- Chạy dưới ngữ cảnh của owner (thường là dbo)
AS
BEGIN
    -- Caller không cần quyền SELECT trực tiếp trên bảng này
    SELECT SensitiveColumn FROM dbo.ProtectedTable;
END;
-- Cấp cho user quyền EXECUTE, không cần SELECT:
GRANT EXECUTE ON dbo.GetRestrictedData TO ReportUser;

-- EXECUTE AS SELF: chạy dưới context của người tạo procedure
-- EXECUTE AS USER = 'specific_user': context cố định
-- EXECUTE AS CALLER: context của người gọi (default)

-- Kiểm tra context hiện tại:
SELECT ORIGINAL_LOGIN() AS original_login,
       USER_NAME() AS current_context,
       SYSTEM_USER AS system_login;

13. SQL Injection Prevention

Các hình thức SQL Injection

-- 1. Classic string injection
-- Input: ' OR '1'='1
-- Query: WHERE Password = '' OR '1'='1' -- Luôn đúng!

-- 2. UNION-based injection
-- Input: ' UNION SELECT username, password FROM sys.sql_logins --
-- Lấy thông tin hệ thống!

-- 3. Stacked queries
-- Input: '; DROP TABLE Users; --
-- Xóa toàn bộ table!

-- 4. Time-based blind injection
-- Input: ' IF (1=1) WAITFOR DELAY '0:0:5' --
-- Detect database structure qua timing

Phòng chống

-- ✅ CÁCH 1: Parameterized queries (bắt buộc!)
-- Application code dùng SqlParameters (C#):
-- cmd.Parameters.AddWithValue("@Name", userInput);

-- ✅ CÁCH 2: sp_executesql với parameters
DECLARE @sql NVARCHAR(500); 
DECLARE @userName NVARCHAR(100) = ''; -- Từ user input

SET @sql = N'SELECT * FROM Users WHERE UserName = @name';
EXEC sp_executesql @sql, N'@name NVARCHAR(100)', @name = @userName;
-- @userName không được interpret là SQL, dù chứa SQL code

-- ✅ CÁCH 3: Stored Procedures
CREATE PROCEDURE dbo.GetUser @UserName NVARCHAR(100)
AS
    SELECT UserId, UserName, Email -- Chỉ expose cần thiết
    FROM dbo.Users
    WHERE UserName = @UserName; -- Safe

-- ✅ CÁCH 4: QUOTENAME cho dynamic object names
DECLARE @TableName NVARCHAR(100) = 'Orders'; -- Từ whitelist
IF @TableName NOT IN ('Orders', 'Products', 'Customers')
    THROW 50001, 'Invalid table', 1;
    
DECLARE @sql NVARCHAR(500) = N'SELECT * FROM ' + QUOTENAME(@TableName);
EXEC sp_executesql @sql;

-- ✅ CÁCH 5: Principle of Least Privilege
-- Application login chỉ có SELECT, INSERT, UPDATE — không DROP, không TRUNCATE
-- Không dùng sa hoặc sysadmin cho application

-- ❌ NEVER: String concatenation với user input
DECLARE @badSql NVARCHAR(500) = 
    'SELECT * FROM Users WHERE Name = ''' + @userInput + ''''; -- VULNERABLE!
EXEC(@badSql);

14. Best Practices Bảo mật Tổng hợp

Checklist Bảo mật Production

-- 1. Vô hiệu hóa tài khoản không dùng
ALTER LOGIN sa DISABLE;
ALTER LOGIN GuestLogin DISABLE;

-- 2. Kiểm tra guest user trong databases
SELECT name FROM sys.databases WHERE is_trustworthy_on = 1; -- Phải là ít
-- Tắt TRUSTWORTHY nếu không cần:
ALTER DATABASE MyDB SET TRUSTWORTHY OFF;

-- 3. Kiểm tra ai có sysadmin
SELECT p.name, p.type_desc
FROM sys.server_principals p
JOIN sys.server_role_members rm ON p.principal_id = rm.member_principal_id
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
WHERE r.name = 'sysadmin';

-- 4. Kiểm tra SQL Logins (nên minimize)
SELECT name, is_disabled FROM sys.sql_logins ORDER BY name;

-- 5. Permissions report
SELECT
    dp.state_desc AS PermissionState,
    dp.permission_name,
    dp.class_desc,
    COALESCE(OBJECT_NAME(dp.major_id), 
             SCHEMA_NAME(dp.major_id), 
             DB_NAME(dp.major_id)) AS SecurableName,
    pr.name AS GranteeName,
    pr.type_desc AS GranteeType
FROM sys.database_permissions dp
JOIN sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id
WHERE dp.state_desc IN ('GRANT', 'DENY')
ORDER BY pr.name, dp.permission_name;

Summary Best Practices