Least privilege, roles, row-level security, and masking

Overview

Least privilege, roles, row-level security, and masking are database security techniques used to limit what users and applications can do and what data they can see. The core idea is simple: grant only the permissions required for a task, through manageable roles, and use database features to reduce accidental or unauthorized exposure of sensitive rows and columns.

This topic matters because databases often contain the most sensitive data in an application: customer records, financial data, health information, employee data, credentials, audit data, and operational secrets. A single over-privileged account, broad role membership, or unmasked reporting connection can turn a small application flaw into a major incident.

For interviews, this topic tests practical security judgment. Strong candidates can explain principals, securables, permissions, server roles, database roles, ownership, GRANT, DENY, REVOKE, row-level security predicates, dynamic data masking, and why masking is not the same as encryption or authorization.

Core Concepts

Least Privilege

Least privilege means each user, service account, job, and application identity receives only the permissions needed to perform its job.

Examples:

• A reporting user gets SELECT on reporting views, not db_owner.
• An API identity gets execute permission on specific procedures, not broad table write access.
• A migration identity gets schema-change permissions only during deployment.
• A support user gets masked read access, not raw access to sensitive columns.

Least privilege limits blast radius when credentials are leaked or code is compromised.

Principals, Securables, And Permissions

SQL Server security has three core ideas:

• Principal: who can request access, such as a login, user, role, or application role.
• Securable: what can be protected, such as a server, database, schema, table, view, procedure, or column.
• Permission: what action is allowed or denied, such as SELECT, INSERT, UPDATE, EXECUTE, ALTER, or CONTROL.

Example:

GRANT SELECT ON SCHEMA::reporting TO role_report_reader;
GRANT EXECUTE ON SCHEMA::api TO role_api_executor;
DENY SELECT ON dbo.CustomerSensitiveData TO role_report_reader;

Grant permissions at the right scope. Schema-level grants are often easier to manage than many object-level grants, but they must match ownership boundaries.

GRANT, DENY, And REVOKE

GRANT gives a permission. DENY explicitly blocks a permission. REVOKE removes a grant or deny.

GRANT SELECT ON dbo.Customer TO role_customer_reader;
DENY SELECT ON dbo.CustomerSalary TO role_customer_reader;
REVOKE SELECT ON dbo.Customer FROM role_customer_reader;

DENY can override grants through other roles, so use it carefully. Too many denies can make permission troubleshooting painful.

Server-Level Roles

Server-level roles grant permissions across the SQL Server instance. Some fixed server roles are very powerful. For example, sysadmin can perform any activity, and securityadmin should be treated with extreme care because it can manage access.

Modern SQL Server versions include more granular server roles designed around least privilege, such as roles for reading server state or managing logins without granting full administrative power.

Avoid giving applications or normal users broad server roles.

Database-Level Roles

Database-level roles group database permissions.

Common fixed database roles include:

• db_datareader
• db_datawriter
• db_ddladmin
• db_owner

Fixed roles are convenient, but they may be broader than needed. Custom roles are often better for applications.

CREATE ROLE role_order_api;

GRANT EXECUTE ON SCHEMA::orders TO role_order_api;

ALTER ROLE role_order_api
ADD MEMBER app_order_service;

Prefer meaningful custom roles such as role_invoice_reader or role_order_writer over blanket roles.

Ownership And Schemas

Schemas are useful security boundaries. You can group objects by responsibility and grant permissions at the schema level.

Example:

CREATE SCHEMA reporting;
CREATE SCHEMA api;

GRANT SELECT ON SCHEMA::reporting TO role_report_reader;
GRANT EXECUTE ON SCHEMA::api TO role_application;

This works well when schemas are organized intentionally. If unrelated objects are mixed in one schema, schema-level grants can become too broad.

Stored Procedures As Security Boundaries

Stored procedures can limit access by granting EXECUTE on approved operations instead of granting direct table access.

GRANT EXECUTE ON api.CreateOrder TO app_order_service;

This lets the application perform the intended operation without direct table permissions. However, procedure code must still validate inputs, parameterize dynamic SQL, and avoid elevated execution context mistakes.

Row-Level Security

Row-Level Security uses a security policy and predicate function to filter or block rows based on execution context. It can ensure users only see rows they are allowed to see.

Example tenant filter:

CREATE FUNCTION security.fn_tenant_filter(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS allowed
    WHERE @TenantId = CONVERT(int, SESSION_CONTEXT(N'TenantId'));

CREATE SECURITY POLICY security.TenantPolicy
ADD FILTER PREDICATE security.fn_tenant_filter(TenantId)
ON dbo.Invoice
WITH (STATE = ON);

The application can set session context after authenticating the tenant:

EXEC sys.sp_set_session_context
    @key = N'TenantId',
    @value = 42,
    @read_only = 1;

RLS is powerful for multi-tenant and security-sensitive systems, but it must be tested carefully.

Filter Predicates And Block Predicates

RLS supports:

• Filter predicates, which silently filter rows from read operations.
• Block predicates, which prevent writes that violate the policy.

Filter predicates answer "which rows can the principal see?" Block predicates answer "which rows can the principal insert, update, or delete?"

For multi-tenant applications, both can matter. Read filtering alone may not prevent a user from inserting a row for another tenant unless write controls exist elsewhere.

Dynamic Data Masking

Dynamic Data Masking hides parts of sensitive column values from users without full unmasked permission.

Example:

CREATE TABLE dbo.Customer
(
    CustomerId int PRIMARY KEY,
    Email nvarchar(320) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    CreditCardNumber varchar(19) MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)') NULL
);

Masking is useful for reducing accidental exposure in support tools, reports, and non-privileged access. It is not a substitute for permissions, encryption, or data classification.

Masking Is Not Encryption

Dynamic Data Masking changes how data is displayed to users without unmask permission. It does not encrypt the stored data and should not be treated as strong protection against highly privileged users or inference attacks.

Use masking to reduce casual exposure. Use permissions, encryption, auditing, and application authorization for stronger protection.

Application Identity Design

Application identities should be scoped by responsibility.

Poor design:

• One shared db_owner account for all applications.
• Same credentials in dev, test, and production.
• Human users sharing application credentials.

Better design:

• Separate identities for read API, write API, jobs, migrations, and reporting.
• Least-privilege roles.
• Managed secret storage.
• Auditing and rotation.
• No shared human access through app accounts.

Auditing And Review

Least privilege requires ongoing review. Permissions drift over time.

Review:

• Role membership.
• Direct user grants.
• Membership in powerful fixed roles.
• Orphaned users and old service accounts.
• Broad schema grants.
• CONTROL, ALTER, and ownership permissions.
• Users with unmasked access.
• RLS bypass paths.

Security design is not a one-time script.

Common Mistakes

Common mistakes include:

• Giving applications db_owner.
• Using sysadmin for jobs that need one specific permission.
• Granting direct user permissions instead of roles.
• Assuming masking is encryption.
• Using RLS without testing write paths.
• Forgetting owners, admins, and privileged users may bypass protections.
• Mixing unrelated objects in one schema and granting broad schema permissions.
• Sharing application credentials with humans.
• Not reviewing role membership over time.

Best Practices

Best practices include:

• Use least privilege by default.
• Prefer custom roles for applications.
• Grant permissions to roles, not individual users.
• Use schemas as intentional security boundaries.
• Use stored procedures or views for controlled access where appropriate.
• Use RLS for tenant or row ownership filtering when it belongs in the database.
• Use masking to reduce accidental exposure, not as the only protection.
• Separate human and workload identities.
• Review permissions regularly.
• Monitor privileged actions and failed access.