Security & Administration - Tổng quan

Phần này bao gồm các kiến thức về Bảo mật (Security) và Quản trị (Administration) SQL Server — những chủ đề quan trọng không chỉ cho DBA mà còn cho Developer khi thiết kế ứng dụng an toàn.

Các chủ đề con

Q&A Phỏng vấn

🟢 Junior Level

Q1: Hai chế độ Authentication trong SQL Server là gì?

A:

• Windows Authentication (Integrated Security): Xác thực qua Active Directory. SQL Server tin tưởng Windows đã xác thực user. Bảo mật hơn vì không cần password trong connection string. Là chế độ khuyến nghị.
• SQL Server Authentication: User/password được lưu trong SQL Server, không phụ thuộc vào Windows. Cần thiết khi connect từ non-Windows systems (Linux, Mac, containers).
• Mixed Mode: Hỗ trợ cả hai. Nên tắt SQL Server Authentication nếu không cần thiết (nguyên tắc least privilege).

Q2: Sự khác biệt giữa Login và User trong SQL Server là gì?

A:

• Login: Tồn tại ở server level — đây là authentication principal, cho phép kết nối vào SQL Server instance.
• User: Tồn tại ở database level — là authorization principal trong database cụ thể, mapping đến Login.

Login [DOMAIN\HuyNgo]  ←→  User [HuyNgo] trong database SalesDB
(Server level)               (Database level)

Một Login có thể map đến User trong nhiều databases. Nếu không có User mapping, Login không thể truy cập database.

Q3: GRANT, DENY, REVOKE khác nhau như thế nào?

A:

• GRANT: Cấp quyền cho principal.
• REVOKE: Xóa bỏ quyền đã GRANT hoặc DENY (về trạng thái “không có quyền”).
• DENY: Từ chối tường minh quyền — DENY luôn thắng GRANT dù user được GRANT qua role.

GRANT SELECT ON dbo.Products TO UserA;  -- UserA có thể SELECT
DENY SELECT ON dbo.Orders TO UserA;     -- UserA KHÔNG thể SELECT Orders dù qua role nào
REVOKE SELECT ON dbo.Products TO UserA; -- UserA không còn quyền SELECT Products được GRANT trực tiếp

Q4: SQL Injection là gì và cách phòng chống?

A: SQL Injection là tấn công khi attacker chèn SQL code độc hại vào input để thay đổi query logic.

-- VULNERABLE: String concatenation
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Users WHERE Name = ''' + @input + '''';
-- Input: ' OR '1'='1 → Lấy được tất cả users!

-- SAFE: Parameterized query
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Users WHERE Name = @name';
EXEC sp_executesql @sql, N'@name NVARCHAR(100)', @name = @input;

-- SAFE: Stored procedure với parameters
CREATE PROCEDURE dbo.GetUser @Name NVARCHAR(100)
AS SELECT * FROM Users WHERE Name = @Name;
-- Parameter không được interpret là SQL code

Q5: Các Fixed Database Roles phổ biến là gì?

A:

Q6: Làm thế nào để tạo Login và User cho một ứng dụng?

A:

-- Server level: Tạo Login
CREATE LOGIN AppLogin WITH PASSWORD = 'Str0ng!P@ssw0rd';

-- Database level: Tạo User và map với Login
USE MyDatabase;
CREATE USER AppUser FOR LOGIN AppLogin;

-- Gán Role
ALTER ROLE db_datareader ADD MEMBER AppUser;
ALTER ROLE db_datawriter ADD MEMBER AppUser;

-- Hoặc GRANT cụ thể
GRANT EXECUTE ON SCHEMA::dbo TO AppUser;

Q7: Transparent Data Encryption (TDE) là gì?

A: TDE mã hóa toàn bộ database files (data file .mdf, log file .ldf, backup files) ở tầng storage — “encryption at rest”. Dữ liệu được decrypt tự động khi đọc vào memory, không cần thay đổi application code.

Bảo vệ chống: Kẻ tấn công lấy file .mdf trực tiếp từ disk sẽ không đọc được.
Không bảo vệ: Ai có quyền query database vẫn đọc được dữ liệu bình thường.

Q8: Backup types trong SQL Server là gì?

A:

Q9: Recovery Models trong SQL Server là gì?

A:

Q10: sp_who2 và Activity Monitor dùng để làm gì?

A:

• sp_who2: Stored procedure hiển thị tất cả active sessions, blocking info, CPU/Disk usage. Dùng để diagnose nhanh.
• Activity Monitor: GUI trong SSMS hiển thị processes, waits, expensive queries, data file I/O.

EXEC sp_who2; -- Xem tất cả sessions
EXEC sp_who2 'active'; -- Chỉ active sessions
EXEC sp_who2 55; -- Session cụ thể

🟡 Mid Level

Q11: Row-Level Security (RLS) là gì và dùng để làm gì?

A: RLS cho phép kiểm soát quyền truy cập ở cấp row dựa trên người dùng đang query. Một table có thể trả về các rows khác nhau cho các user khác nhau — hoàn toàn transparent với application.

-- Ví dụ: Mỗi sales rep chỉ thấy orders của mình
CREATE FUNCTION dbo.fn_SecurityPredicate(@SalesRepId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
    SELECT 1 AS result
    WHERE @SalesRepId = CAST(SESSION_CONTEXT(N'SalesRepId') AS INT)
        OR IS_MEMBER('db_owner') = 1; -- Managers thấy tất cả

CREATE SECURITY POLICY SalesRepPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(SalesRepId) ON dbo.Orders,
ADD BLOCK PREDICATE dbo.fn_SecurityPredicate(SalesRepId) ON dbo.Orders
WITH (STATE = ON);

-- Application set context trước khi query:
EXEC sys.sp_set_session_context @key = N'SalesRepId', @value = 42;
SELECT * FROM Orders; -- Tự động filter chỉ orders của SalesRepId = 42

Q12: Dynamic Data Masking (DDM) là gì?

A: DDM che giấu dữ liệu nhạy cảm cho các user không có quyền xem — không thay đổi dữ liệu thực, chỉ thay đổi cách hiển thị.

CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    FullName NVARCHAR(100) MASKED WITH (FUNCTION = 'partial(2,"...",2)'),      -- "Hu...go"
    Email NVARCHAR(200) MASKED WITH (FUNCTION = 'email()'),                    -- "hXX@XXXX.com"
    Phone NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)'),   -- "XXX-XXX-1234"
    CreditCard NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)'), -- "XXXX-XXXX-XXXX-5678"
    Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'random(1, 100)')            -- Random số
);

-- User thường thấy:
-- FullName: "Hu...go", Email: "hXX@XXXX.com"

-- User có quyền UNMASK thấy dữ liệu thật:
GRANT UNMASK ON dbo.Customers TO PowerUser;
-- Hoặc UNMASK toàn database:
GRANT UNMASK TO PowerUser;

Q13: EXECUTE AS là gì và khi nào dùng?

A: EXECUTE AS cho phép thay đổi security context khi thực thi code — impersonate một user/login khác.

-- Stored procedure chạy dưới context của owner (không phải caller)
CREATE PROCEDURE dbo.GetSensitiveData
WITH EXECUTE AS OWNER -- hoặc EXECUTE AS 'SpecificUser'
AS
BEGIN
    -- Thực thi với quyền của OWNER dù caller không có quyền
    SELECT * FROM dbo.SensitiveTable;
END;

-- Gọi procedure:
-- User A không có SELECT trên SensitiveTable
-- Nhưng User A có EXECUTE quyền trên procedure
-- → Cho phép! (Ownership chaining)

-- Impersonation tạm thời:
EXECUTE AS USER = 'LimitedUser';
SELECT * FROM dbo.Orders; -- Thực thi dưới quyền LimitedUser
REVERT; -- Quay lại context gốc

Q14: Always Encrypted là gì? Khác TDE như thế nào?

A:

-- Always Encrypted: Dữ liệu được mã hóa TRƯỚC khi tới SQL Server
-- SQL Server chỉ thấy ciphertext, không thể đọc plaintext

-- Cột được tạo với encryption:
CREATE TABLE Patients (
    PatientId INT PRIMARY KEY,
    Name NVARCHAR(100),
    SSN NVARCHAR(11) ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = SSN_CEK,
        ENCRYPTION_TYPE = Randomized, -- hoặc Deterministic
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    )
);
-- DBA query SELECT SSN FROM Patients → thấy ciphertext, không đọc được
-- Application (có CMK) → thấy plaintext

Q15: SQL Server Audit là gì? Thiết lập như thế nào?

A: SQL Server Audit cho phép track và log các hành động security/database vào file, Windows Event Log, hoặc Application Log.

-- Tạo Server Audit
CREATE SERVER AUDIT SecurityAudit
TO FILE (FILEPATH = 'C:\SQLAudit\', MAXSIZE = 100 MB, MAX_FILES = 10)
WITH (ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT SecurityAudit WITH (STATE = ON);

-- Audit đăng nhập thất bại
CREATE SERVER AUDIT SPECIFICATION LoginAuditSpec
FOR SERVER AUDIT SecurityAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP);
ALTER SERVER AUDIT SPECIFICATION LoginAuditSpec WITH (STATE = ON);

-- Database audit: Track DML trên table nhạy cảm
CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAudit
FOR SERVER AUDIT SecurityAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Customers BY public)
WITH (STATE = ON);

-- Xem audit log
SELECT 
    event_time,
    action_id,
    succeeded,
    session_server_principal_name AS login_name,
    statement,
    server_instance_name
FROM sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Q16: Schema-based permissions là gì? Lợi ích?

A: Thay vì GRANT quyền trên từng object, có thể GRANT trên toàn bộ schema — tất cả objects trong schema tự động inherit permission.

-- Tạo schema riêng cho app
CREATE SCHEMA app AUTHORIZATION dbo;

-- Tạo objects trong schema
CREATE TABLE app.Orders (...);
CREATE TABLE app.Products (...);
CREATE PROCEDURE app.GetOrders AS ...;

-- GRANT một lần cho toàn schema (thay vì từng object)
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO AppUser;
GRANT EXECUTE ON SCHEMA::app TO AppUser;
-- AppUser tự động có quyền trên mọi object trong schema app

-- Optional: Tạo schema riêng cho reporting (read-only)
CREATE SCHEMA rpt AUTHORIZATION dbo;
GRANT SELECT ON SCHEMA::rpt TO ReportUser;

Q17: Backup Strategy tốt nhất là gì?

A: 3-2-1 Rule cho database backup:

• 3 copies của data
• 2 different storage types
• 1 offsite/cloud copy

Lịch backup thực tế:

-- Daily Full Backup (11 PM)
BACKUP DATABASE SalesDB 
TO DISK = 'D:\Backups\SalesDB_Full_' + CONVERT(VARCHAR,GETDATE(),112) + '.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Every 6 hours: Differential Backup
BACKUP DATABASE SalesDB 
TO DISK = 'D:\Backups\SalesDB_Diff_' + CONVERT(VARCHAR,GETDATE(),112) + '_' + ...
WITH DIFFERENTIAL, COMPRESSION;

-- Every 15-30 minutes: Transaction Log Backup (Recovery Model = FULL)
BACKUP LOG SalesDB 
TO DISK = 'D:\Backups\SalesDB_Log_' + ... + '.trn'
WITH COMPRESSION;

-- Test restore regularly!
RESTORE DATABASE SalesDB_Test 
FROM DISK = '...' 
WITH NORECOVERY, MOVE ..., MOVE ...;
RESTORE LOG SalesDB_Test FROM DISK = '...' WITH RECOVERY;
DBCC CHECKDB(SalesDB_Test); -- Verify integrity

Q18: Làm thế nào để giám sát SQL Server Agent Jobs?

A:

-- Xem tất cả jobs và trạng thái
SELECT 
    j.name AS job_name,
    j.enabled,
    h.run_date,
    h.run_time,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
    END AS last_run_status,
    h.run_duration,
    h.message
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
    AND h.instance_id = (
        SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
        WHERE job_id = j.job_id AND step_id = 0
    )
ORDER BY j.name;

-- Xem jobs đang chạy hiện tại
EXEC msdb.dbo.sp_help_job @execution_status = 1; -- 1 = Running

Q19: Wait Statistics là gì? Dùng để làm gì?

A: Wait Statistics cho biết SQL Server đang “chờ” gì nhiều nhất — là điểm khởi đầu để tìm performance bottleneck.

-- Top wait types (kể từ khi SQL Server restart)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000.0 AS wait_time_seconds,
    max_wait_time_ms / 1000.0 AS max_wait_seconds,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_seconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Lọc bỏ background waits không liên quan
    'SLEEP_TEMPDBSTARTUP', 'SLEEP_DBSTARTUP', 'LAZYWRITER_SLEEP',
    'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SLEEP_TASK',
    'SLEEP_SYSTEMTASK', 'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
    'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Giải thích common wait types:

Q20: Query Store là gì?

A: Query Store là tính năng built-in (SQL Server 2016+) tự động capture query plans và runtime stats. Giúp:

• Phát hiện plan regression (query đột ngột chậm vì plan thay đổi)
• Force execution plan cũ nếu plan mới tệ hơn
• Analyze top expensive queries

-- Bật Query Store
ALTER DATABASE MyDB SET QUERY_STORE = ON;
ALTER DATABASE MyDB SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1024
);

-- Top queries theo CPU
SELECT TOP 10
    qsq.query_id,
    qsq.query_hash,
    SUM(qsrs.avg_cpu_time) AS total_avg_cpu,
    SUM(qsrs.avg_duration) AS total_avg_duration,
    SUM(qsrs.count_executions) AS total_executions,
    SUBSTRING(qsqt.query_sql_text, 1, 200) AS query_text
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
GROUP BY qsq.query_id, qsq.query_hash, qsqt.query_sql_text
ORDER BY total_avg_cpu DESC;

-- Force một plan cụ thể
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 5;

🔴 Senior Level

Q21: Thiết kế Permission Model cho một ứng dụng multi-tenant như thế nào?

A: Có nhiều cách tiếp cận:

Approach 1: Schema-based separation

-- Mỗi tenant có schema riêng
CREATE SCHEMA Tenant1 AUTHORIZATION dbo;
CREATE SCHEMA Tenant2 AUTHORIZATION dbo;

-- App login per tenant
CREATE LOGIN Tenant1Login WITH PASSWORD = '...';
CREATE USER Tenant1User FOR LOGIN Tenant1Login;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Tenant1 TO Tenant1User;
DENY SELECT ON SCHEMA::Tenant2 TO Tenant1User; -- Tường minh deny cross-tenant

-- RLS thêm cho Defense in depth

Approach 2: RLS với TenantId column

-- Tất cả dữ liệu trong cùng schema, RLS filter theo tenant
ALTER TABLE Orders ADD TenantId INT NOT NULL;

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'));

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

Q22: Giải thích Cross-database Ownership Chaining.

A: Khi một stored procedure trong DB_A query sang DB_B, thông thường SQL Server kiểm tra permissions user trên DB_B objects. Với ownership chaining, nếu cùng owner (thường là dbo), SQL Server bỏ qua permission check trên DB_B.

-- DB_A.dbo.GetData procedure:
CREATE PROCEDURE DB_A.dbo.GetCrossDBData
AS
    SELECT * FROM DB_B.dbo.SensitiveTable; -- Cross-DB reference

-- Nếu DB_A.dbo và DB_B.dbo cùng owner:
-- User chỉ cần EXECUTE quyền trên procedure, không cần SELECT trên DB_B.dbo.SensitiveTable

-- Bật/tắt Cross-database ownership chaining:
ALTER DATABASE DB_B SET DB_CHAINING ON;  -- Cho phép
ALTER DATABASE DB_B SET DB_CHAINING OFF; -- Tắt (secure)

-- Server level:
sp_configure 'cross db ownership chaining', 1; -- Bật cho tất cả DB
RECONFIGURE;

Rủi ro: Có thể vô tình mở quyền truy cập cross-database không mong muốn. Best practice: Tắt và dùng explicit permissions hoặc EXECUTE AS.

Q23: Làm thế nào để điều tra và ngăn chặn SQL Injection trong stored procedures?

A:

Phát hiện:

-- Tìm dynamic SQL không dùng sp_executesql (risky patterns)
SELECT 
    OBJECT_NAME(object_id) AS object_name,
    OBJECT_SCHEMA_NAME(object_id) AS schema_name,
    definition
FROM sys.sql_modules
WHERE definition LIKE '%EXEC(%+%'  -- EXEC với string concatenation
   OR definition LIKE '%EXECUTE(%+%'
   OR (definition LIKE '%@%+%' AND definition LIKE '%EXEC%')
ORDER BY object_name;

Phòng chống:

-- BAD: 
DECLARE @sql VARCHAR(500) = 'SELECT * FROM ' + @TableName;
EXEC(@sql); -- Table name injection!

-- GOOD: Whitelist validation
IF @TableName NOT IN ('Orders', 'Products', 'Customers')
    THROW 50001, 'Invalid table name', 1;
DECLARE @sql NVARCHAR(500) = N'SELECT * FROM ' + QUOTENAME(@TableName);
EXEC sp_executesql @sql;

-- QUOTENAME: Wrap identifier in quotes, escape special chars
SELECT QUOTENAME('Orders; DROP TABLE Users--');
-- Trả về: [Orders; DROP TABLE Users--] → An toàn!

-- BEST: Tránh dynamic SQL hoàn toàn nếu có thể

Q24: Thiết kế Backup & Recovery Strategy cho production database 99.99% uptime?

A:

Recovery Point Objective (RPO): Mất tối đa bao nhiêu data? (e.g., 5 phút)
Recovery Time Objective (RTO): Restore trong bao lâu? (e.g., 1 giờ)

Strategy cho RPO=5min, RTO=1h:

-- 1. Cấu hình
ALTER DATABASE CriticalDB SET RECOVERY FULL;

-- 2. Lịch backup
-- Weekly: Full backup (Sunday 2AM)
-- Daily: Differential backup (Mon-Sat 2AM)  
-- Every 5 minutes: Transaction Log backup

-- 3. Always On Availability Groups (HADR)
-- Primary: Xử lý writes
-- Secondary (synchronous): Auto-failover trong vài giây (RTO << 1h)
-- Secondary (async, different DC): DR copy (địa lý khác nhau)

-- 4. Monitor backup
SELECT 
    bs.database_name,
    bs.type AS backup_type, -- D=Full, I=Differential, L=Log
    bs.backup_finish_date,
    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS duration_minutes,
    bs.backup_size / 1024 / 1024 AS size_MB
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'CriticalDB'
ORDER BY bs.backup_finish_date DESC;

-- 5. Test restore quarterly (drills!)
-- Point-in-Time Recovery test:
RESTORE DATABASE CriticalDB_Test FROM DISK = '...' 
WITH NORECOVERY, REPLACE, MOVE ... ;
RESTORE LOG CriticalDB_Test FROM DISK = '...' 
WITH RECOVERY, STOPAT = '2026-04-01 15:30:00'; -- Restore đến thời điểm cụ thể

Q25: Giải thích Always On Availability Groups và cách nó cải thiện bảo mật/availability.

A:

Always On AG Architecture:
┌─────────────────────────────────────────────────────────┐
│  Primary Replica (DC1)          Secondary Replicas       │
│  ┌──────────────────┐    Sync   ┌──────────────────┐   │
│  │ SQL Server A     │ ─────────► │ SQL Server B     │   │
│  │ (Read-Write)     │           │ (Auto-Failover)   │   │
│  └──────────────────┘    Async  └──────────────────┘   │
│                          ──────► SQL Server C (DR site)  │
└─────────────────────────────────────────────────────────┘
                │
         Listener VIP (Virtual IP)
                │
     Application connect to Listener
     → Automatically routes to Primary

Bảo mật với AG:

-- Secondary readable replicas cho reporting (không load primary)
-- Configure readable secondary:
ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'SQL-B' 
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Backup từ secondary (giảm tải primary)
ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'SQL-B'
WITH (BACKUP_PRIORITY = 90); -- Ưu tiên backup từ secondary