Azure Synapse Column-Level Security — Restrict Column Access with GRANT
You need to implement column-level security in Azure Synapse Analytics to restrict access to salary information. Only users with the 'HRManager' role should see salary columns. Which feature should you use?
Quick Answer
Column-level security does exactly what its name suggests: it lets you grant SELECT permission on specific columns of a table to specific users or roles, so a query from someone without that grant simply cannot retrieve data from the restricted column at all, and fails outright rather than returning something. Setting the GRANT to apply only to the salary columns and only to the HRManager role means anyone outside that role loses access to just those columns while retaining normal access to the rest of the table. Row-level security restricts which entire rows a user can see, filtering the table horizontally rather than restricting specific columns, so it would not help when the goal is hiding one type of information across every row. Dynamic data masking looks similar since it also affects what a user sees in a column, but it works by obscuring or replacing the values shown, not by blocking access outright; the underlying access still happens, just with masked output rather than a true security boundary. Azure Purview is a data governance and cataloging tool, useful for discovering and classifying sensitive data, but it does not enforce access restrictions. When you see a requirement to restrict specific columns to a specific role, column-level security via GRANT is the mechanism built for exactly that.
⚠ Common exam trap
Candidates may confuse row-level security (RLS) with column-level security. RLS controls row access via security predicates, while CLS controls column access via GRANT.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Column-level security using GRANT on columns
Column-level security (CLS) in Azure Synapse Analytics uses GRANT on specific columns to restrict access. Option A is incorrect because row-level security filters rows, not columns. Option B is incorrect because dynamic data masking obfuscates data but does not prevent access. Option D is incorrect because Azure Purview is a governance tool, not a security enforcement mechanism.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Row-level security using security predicates
Why it's wrong here
Row-level security filters which rows a query returns via a predicate; it cannot hide or deny a column. It is the right choice when restricting rows by tenant, region, or user identity. Here the requirement is to conceal the salary columns themselves, which needs column-level GRANT/DENY.
- ✗
Dynamic data masking
Why it's wrong here
Dynamic data masking returns masked values to unauthorised users rather than denying access to the column, and HRManager would still see masked output unless explicitly exempted. It suits obfuscating sensitive values in query results, not granting full column visibility only to a specific role.
- ✓
Column-level security using GRANT on columns
Why this is correct
GRANT on specific columns lets you deny salary data to everyone except HRManager, since permissions are enforced by the database engine at query time regardless of the client tool. This directly satisfies the stem's requirement to restrict salary columns by role, unlike object-level or row-level alternatives.
- ✗
Azure Purview data classification
Why it's wrong here
Azure Purview data classification labels and catalogues sensitive data; it does not enforce query-time access control in the SQL pool. It is the right choice for discovering and governing data assets across sources. Enforcing column visibility for the HRManager role requires T-SQL GRANT and DENY on the salary columns.
Go deeper
Related to this question
Learn chapter
Implement Azure Synapse Analytics
Key term
Azure Synapse Analytics
Azure Synapse Analytics is a cloud-based data integration, warehousing, and analytics service that brings together big data and data warehouse capabilities under one platform.
Key term
Azure Data Masking
Azure Data Masking is a security feature that hides sensitive data in database query results by replacing it with obscured characters, so unauthorized users see a blurred version instead of the real information.
About these practice questions
This DP-203 question is part of Courseiva's 509-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-203
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. An organization is using Azure Synapse Analytics and wants to implement column-level security to restrict access to sensitive columns. Which feature should they use?
easy- A.Dynamic data masking
- B.Azure Purview
- ✓ C.Column-level security using GRANT
- D.Row-level security
Why C: Column-level security in Azure Synapse Analytics is implemented using GRANT statements on specific columns, restricting access to sensitive columns. Option A is incorrect because dynamic data masking obfuscates data at query time but does not prevent access. Option B is incorrect because Azure Purview is a data governance service, not for access control. Option D is incorrect because row-level security filters rows, not columns.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-203 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-203 exam.