A healthcare organization is migrating its patient records system to Azure. The system requires that all data stored in Azure SQL Database must be encrypted at rest and in transit. Additionally, the organization needs to implement row-level security to ensure that doctors can only view patient records relevant to their assigned patients, and auditors can only view a subset of columns for all patients. Which combination of Azure SQL Database features should be implemented?
- ATransparent Data Encryption (TDE), Secure Sockets Layer (SSL) connection, and Row-Level Security (RLS)
- BAzure SQL Database Firewall, Secure Sockets Layer (SSL) connection, and Row-Level Security (RLS)
- CTransparent Data Encryption (TDE), Always Encrypted, and Dynamic Data Masking
- DAlways Encrypted, Dynamic Data Masking, and Column-Level Security
Show answer & explanationAnswer & explanation
Correct answer: A. Transparent Data Encryption (TDE), Secure Sockets Layer (SSL) connection, and Row-Level Security (RLS)
Transparent Data Encryption (TDE) encrypts data at rest. SSL/TLS ensures data is encrypted in transit. Row-Level Security (RLS) allows granular control over which rows a user can see, addressing the doctor's requirement. Column-Level Security (CLS) would be needed for the auditor's requirement to view only a subset of columns, but it's not an option. However, RLS and CLS are separate concepts. RLS specifically addresses the 'doctors can only view patient records relevant to their assigned patients' requirement. For auditors to view only a subset of columns, CLS, or a view, would be needed. With the given options, RLS is the best fit for the doctor scenario and TDE/SSL for encryption. Since CLS isn't an explicit option, RLS is the closest for granular data viewing control.
Why the other options are wrong
- B. SQL Database Firewall restricts network access, not data access within the database. SSL is correct, but the firewall doesn't address row-level security.
- C. Always Encrypted also encrypts data at rest and in transit, but TDE is simpler for 'at rest'. Dynamic Data Masking obfuscates data, not restricts rows or columns based on roles.
- D. Column-Level Security is a valid concept for restricting columns, but it's not a direct 'feature' like RLS. Dynamic Data Masking is not for restrictive access. Always Encrypted is for encryption but doesn't handle row/column restriction on its own.
SQL DB Security Features
Azure SQL Database offers multiple security features including Transparent Data Encryption (TDE) for data at rest, SSL/TLS for data in transit, and Row-Level Security (RLS) for granular access control over rows based on user identity.
- TDE: Encrypts entire database at rest
- SSL/TLS: Encrypts data communicated over network
- RLS: Filters rows returned by queries based on user context
- CLS: Restricts access to specific columns (not a direct option here but relevant)
Memory trick: TDE is Rest, SSL is Transit, RLS is Rows.