SQL 93 🛢️ Row-Level Security and Data Masking
Row-level security (RLS) and data masking are two distinct controls that are frequently mentioned together because they both restrict what a user can see. They operate at different granularities, however, and they solve different problems. RLS filters which rows a query returns—a user in one department sees only that department’s records, while a user in another sees only theirs . Data masking obfuscates which values appear in columns that the user is already permitted to query—a support representative can see the customer record, but the credit card column shows XXXX-XXXX-XXXX-1234 instead of the real number . Neither replaces the other, and production systems often need both.
The implementation story differs sharply by database engine. PostgreSQL and SQL Server have native, first-class RLS; MySQL and SQLite do not . Data masking is even more fragmented: SQL Server and Azure SQL have Dynamic Data Masking built in, Oracle has Data Redaction, MySQL’s masking is an Enterprise Edition plugin, and PostgreSQL relies on views, functions, or the anon extension . This chapter covers the mechanics of each control, the engines that support them natively, and the patterns for combining them into a layered defense.
Key point: RLS filters rows using a policy predicate evaluated against the current user or session context; data masking replaces sensitive column values with obfuscated equivalents in query results. RLS is native to PostgreSQL and SQL Server; masking is native to SQL Server/Azure SQL and Oracle, and must be built manually or via extensions elsewhere.
Why both controls are necessary
The row isolation problem. In a multi-tenant application, a single orders table holds rows for every customer. Table-level GRANT SELECT gives a user access to every row. RLS adds a policy that silently filters the result set: a customer sees only rows where customer_id matches their identity . The filtering happens inside the database, so application code cannot forget to add a WHERE clause. This is the control that prevents cross-tenant data leaks.
The column exposure problem. Even within an authorized row, some columns are more sensitive than others. A support agent needs the customer’s name and order history to do their job, but does not need the full credit card number. RLS does nothing here—the row is authorized. Data masking replaces the sensitive value in the query result with a formatted placeholder, so the agent sees enough to identify the record but not enough to steal the card . The underlying data remains unchanged; masking is applied at result-return time.
The persistence vs. display distinction. RLS is enforcement: the filtered rows are not returned to the client at all. Masking is presentation: the masked value is what the client receives, but the database still stores the real value. A user with UNMASK permission sees the real value; a user without it sees the mask . This makes masking suitable for casual exposure reduction but not for cryptographic protection—a determined user with direct query access may infer masked values through side channels, which is why Microsoft’s documentation explicitly recommends Always Encrypted for strong protection .
The engine fragmentation problem. RLS is not portable. PostgreSQL’s CREATE POLICY syntax is different from SQL Server’s CREATE SECURITY POLICY, and MySQL has no native RLS at all . A team that develops on PostgreSQL and deploys on MySQL must implement row filtering in application code or a proxy. Masking is similarly fragmented: SQL Server’s MASKED WITH (FUNCTION = ...) has no direct equivalent in PostgreSQL, where you would use a view or a custom function .
The performance and complexity trade-off. RLS policies are evaluated for every row the query touches. A simple equality predicate is cheap; a predicate that joins to a hierarchy table or calls a function that queries other tables is not. SQL Server’s documentation warns against excessive joins and recursion in predicate functions . Masking is cheaper—it transforms values in the result set—but it adds CPU overhead per row and per masked column. Both controls add complexity to debugging: a query that “returns nothing” might be blocked by RLS, and a query that “shows garbage” might be masked, and neither is obvious without knowing the policies.
a. Row-level security: PostgreSQL and SQL Server
PostgreSQL implements RLS through policies attached to tables. The table must first be enabled with ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Once enabled, all normal access is blocked unless a policy allows it—this is deny-by-default, and it is a common footgun because enabling RLS without creating a policy makes the table invisible to non-superusers .
A policy specifies the command (SELECT, INSERT, UPDATE, DELETE, or ALL), the roles it applies to, and the USING expression that determines visibility. For a customer isolation scenario:
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY customer_orders ON orders
FOR SELECT
USING (customer_id = current_setting('app.customer_id')::int);The current_setting('app.customer_id') reads a session variable that the application sets after authentication. This is the standard pattern for connection-pooled applications: the application runs SET app.customer_id = '42' on the connection before executing queries . The policy compares the row’s customer_id to that session value and filters accordingly.
PostgreSQL also supports WITH CHECK for write operations, which validates that new or modified rows satisfy the policy. Without a WITH CHECK, an UPDATE policy that uses only USING allows a user to modify a row so that it no longer matches the policy, effectively moving it out of their view . The passwd example in the PostgreSQL documentation demonstrates a policy that restricts shell changes as well as row ownership, showing that WITH CHECK can enforce value constraints, not just ownership.
SQL Server implements RLS through inline table-valued functions and security policies. The function returns 1 when access is allowed and takes column values as parameters:
CREATE FUNCTION Security.fn_TenantFilter(@TenantID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS int);The policy binds the function to the table:
CREATE SECURITY POLICY TenantPolicy
ADD FILTER PREDICATE Security.fn_TenantFilter(TenantID) ON dbo.Orders
WITH (STATE = ON);SQL Server distinguishes filter predicates (which hide rows from SELECT, UPDATE, DELETE) from block predicates (which prevent INSERT or UPDATE operations that would violate the policy) . Block predicates are essential for multi-tenant write paths: without one, a user could insert a row with a different tenant ID and then lose access to the row they just created.
b. Data masking: native implementations and manual patterns
SQL Server and Azure SQL’s Dynamic Data Masking is configured per column with a masking function. The function determines what non-privileged users see:
ALTER TABLE Customers
ALTER COLUMN CreditCardNumber ADD MASKED WITH (FUNCTION = 'creditcard()');The available functions include default() (full mask), email() (partial email), creditcard() (last four digits visible), random(start, end) (random number in range), and partial(prefix, padding, suffix) for custom patterns . The masking is applied at query time; users with UNMASK permission see the real value. GRANT UNMASK ON dbo.Customers(CreditCardNumber) TO FinanceRole grants column-level unmasking, allowing a finance role to see real card numbers while the support role sees masked values .
PostgreSQL has no built-in masking function. The standard alternatives are views and custom functions. A view that exposes masked columns:
CREATE VIEW masked_customers AS
SELECT id, name,
'XXXX-XXXX-XXXX-' || right(card_number, 4) AS card_number
FROM customers;Granting SELECT on the view instead of the table gives the user masked data. The limitation is that the view must be maintained as the schema changes, and any direct table access bypasses the mask. A more robust approach uses the anon extension (formerly postgresql_anonymizer), which provides declarative masking rules and dynamic masking based on role, but it is a third-party extension rather than a core feature.
Oracle’s Data Redaction works similarly to SQL Server’s DDM: a policy is attached to a table and column, and the redaction is applied at query execution time. The redaction types include full, partial, random, regular expression, and nullify . MySQL’s masking is an Enterprise Edition plugin with functions like mask_pan() and mask_ssn() that transform values in queries . Neither is available in the Community Edition, which means MySQL Community users must implement masking in views or application code.
c. Combining RLS and masking, and the operational caveats
RLS and masking are complementary, and the order of evaluation matters conceptually. RLS determines which rows are visible; masking determines which column values are visible within those rows. A customer support agent with RLS scoped to their region sees only their region’s orders, and within those orders, the card number column is masked. The finance role might bypass RLS for reporting but still see masked SSNs unless granted UNMASK on that specific column .
The operational caveats are significant. RLS policies can cause performance problems if the predicate function is expensive. SQL Server documentation warns against type conversions, recursion, and excessive joins in predicate functions because the optimizer cannot always optimize through them . PostgreSQL policies that call functions querying other tables can turn a simple SELECT into a multi-table join. The recommendation is to keep predicates simple—a direct equality against a session variable is ideal—and to index the columns used in the predicate.
Masking has its own sharp edges. SQL Server’s DDM does not prevent inference attacks: a user who can run arbitrary queries can deduce masked values by filtering on the masked column or using functions . The documentation is explicit that DDM is for “casual unauthorized viewing” and that Always Encrypted is required for cryptographic protection. PostgreSQL views that mask data can be bypassed if the user has direct table access, which means the grant structure must be carefully controlled.
A final caveat applies to both: superusers and table owners typically bypass RLS and masking. PostgreSQL’s BYPASSRLS attribute allows a role to skip policies entirely; table owners bypass RLS unless FORCE ROW LEVEL SECURITY is set . SQL Server’s sysadmin and db_owner roles see unmasked data. Testing must be done with a non-privileged user, or the controls will appear to work when they are not being enforced for the account you are testing with.
Complete Example Session
The following session builds a PostgreSQL table with RLS and a masking view, then tests both controls with a non-privileged role.
-- ============================================
-- PART 1: CREATE TABLE AND ROLES
-- ============================================
CREATE TABLE orders (
id serial PRIMARY KEY,
customer_id int NOT NULL,
product text NOT NULL,
card_number text NOT NULL
);
CREATE ROLE support_role;
CREATE ROLE finance_role;
-- ============================================
-- PART 2: INSERT SAMPLE DATA
-- ============================================
INSERT INTO orders (customer_id, product, card_number) VALUES
(1, 'Widget', '4111111111111111'),
(1, 'Gadget', '4222222222222222'),
(2, 'Widget', '4333333333333333');
-- ============================================
-- PART 3: ENABLE RLS AND CREATE POLICY
-- ============================================
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY support_own_customers ON orders
FOR SELECT
TO support_role
USING (customer_id = current_setting('app.customer_id')::int);
-- ============================================
-- PART 4: GRANT TABLE ACCESS
-- ============================================
GRANT SELECT ON orders TO support_role;
GRANT SELECT ON orders TO finance_role;
-- ============================================
-- PART 5: CREATE MASKING VIEW FOR SUPPORT
-- ============================================
CREATE VIEW orders_masked AS
SELECT id, customer_id, product,
'XXXX-XXXX-XXXX-' || right(card_number, 4) AS card_number
FROM orders;
GRANT SELECT ON orders_masked TO support_role;
REVOKE SELECT ON orders FROM support_role;
GRANT SELECT ON orders_masked TO support_role;
-- ============================================
-- PART 6: TEST AS SUPPORT_ROLE (CUSTOMER 1)
-- ============================================
SET ROLE support_role;
SET app.customer_id = '1';
SELECT * FROM orders_masked;
-- Returns rows for customer 1 only, with masked card numbers
-- ============================================
-- PART 7: TEST RLS FILTERING
-- ============================================
SET app.customer_id = '2';
SELECT * FROM orders_masked;
-- Returns only customer 2's row
-- ============================================
-- PART 8: TEST FINANCE_ROLE (NO RLS POLICY)
-- ============================================
RESET ROLE;
SET ROLE finance_role;
SELECT * FROM orders;
-- ERROR: permission denied (no policy for finance_role)
-- Must add a policy or grant BYPASSRLS
-- ============================================
-- PART 9: ADD FINANCE POLICY
-- ============================================
RESET ROLE;
CREATE POLICY finance_all ON orders
FOR SELECT
TO finance_role
USING (true);
-- ============================================
-- PART 10: TEST FINANCE (ALL ROWS, REAL VALUES)
-- ============================================
SET ROLE finance_role;
SELECT * FROM orders;
-- Returns all rows with real card numbersThe ten parts cover table and role creation, sample data, RLS policy creation, grants, a masking view, support role testing with RLS and masking, switching customer context, the finance role being blocked by deny-by-default RLS, adding a permissive finance policy, and verifying that finance sees real values. What the session demonstrates is that RLS and masking are independent controls: the support role sees filtered rows and masked columns, while the finance role sees all rows with real values because it has a different policy and access to the unmasked table.
Quick Reference
RLS Support by Engine
| Engine | Native RLS | Mechanism |
|---|---|---|
| PostgreSQL | Yes | CREATE POLICY, ENABLE ROW LEVEL SECURITY |
| SQL Server | Yes (2016+) | CREATE SECURITY POLICY, predicate functions |
| MySQL | No | Application-layer filtering |
| Oracle | Yes | Virtual Private Database (VPD) |
Masking Support by Engine
| Engine | Native Masking | Mechanism |
|---|---|---|
| SQL Server / Azure SQL | Yes | Dynamic Data Masking, MASKED WITH |
| Oracle | Yes | Data Redaction policies |
| MySQL | Enterprise only | mask_pan(), mask_ssn() plugin |
| PostgreSQL | No (core) | Views, functions, anon extension |
SQL Server Masking Functions
| Function | Example Output |
|---|---|
default() | XXXX or 0 |
email() | aXXX@XXXX.com |
creditcard() | XXXX-XXXX-XXXX-1234 |
random(1, 100) | Random number in range |
partial(1, "XXX", 0) | 5XXXXXXX |
Key Operational Notes
| Concern | Detail |
|---|---|
| RLS deny-by-default | Enabling RLS without a policy hides the table |
WITH CHECK | Required to prevent updates that move rows out of policy |
| SQL Server block predicates | Prevent inserts/updates that violate policy |
| Masking inference | DDM does not prevent determined inference attacks |
| Superuser bypass | BYPASSRLS (PostgreSQL), sysadmin (SQL Server) |
Best Practices
✅ Do This:
ALTER TABLE orders ENABLE ROW LEVEL SECURITY; -- ✅ enable RLS
CREATE POLICY p ON orders USING (tenant_id = current_setting('app.tenant')::int); -- ✅ simple predicate
CREATE POLICY p ON orders FOR UPDATE USING (...) WITH CHECK (...); -- ✅ check on writes
ALTER TABLE Customers ALTER COLUMN ssn ADD MASKED WITH (FUNCTION = 'default()'); -- ✅ mask
GRANT UNMASK ON dbo.Customers(ssn) TO FinanceRole; -- ✅ selective unmask❌ Don’t Do This:
ALTER TABLE orders ENABLE ROW LEVEL SECURITY; -- ❌ no policy = invisible table
CREATE POLICY p ON orders USING (complex_function_that_joins(...)); -- ❌ slow predicate
-- rely on DDM for cryptographic protection -- ❌ inference attacks
-- test RLS as table owner -- ❌ owner bypasses RLSCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Table invisible after enabling RLS | No policy created | Create a policy for each role/command |
| RLS not filtering | Testing as owner or superuser | Test with a non-privileged role |
| Masked data still visible | User has UNMASK or table access | Revoke direct table access, use views |
| Performance degradation | Expensive predicate function | Keep predicates simple, index columns |
| PostgreSQL view mask bypassed | Direct table GRANT SELECT also exists | Revoke table access, grant only view |
| SQL Server block predicate missing | Inserts bypass RLS filter | Add block predicates for INSERT/UPDATE |
Real-World Examples
1. PostgreSQL tenant isolation
CREATE POLICY tenant ON orders USING (tenant_id = current_setting('app.tenant')::int);2. SQL Server filter predicate
CREATE SECURITY POLICY TenantPolicy
ADD FILTER PREDICATE Security.fn_Tenant(TenantID) ON dbo.Orders;3. SQL Server column masking
ALTER TABLE Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');4. Selective unmask for finance
GRANT UNMASK ON dbo.Customers(CreditCardNumber) TO FinanceRole;5. PostgreSQL masking view
CREATE VIEW masked AS SELECT id, 'XXX-XX-' || right(ssn,4) AS ssn FROM users;6. RLS with session context (SQL Server)
EXEC sp_set_session_context @key=N'TenantID', @value=42;7. RLS bypass for admin (PostgreSQL)
CREATE ROLE admin BYPASSRLS;8. Oracle redaction
DBMS_REDACT.ADD_POLICY(...);9. MySQL Enterprise masking
SELECT mask_ssn(ssn) FROM users;10. Force RLS for table owner (PostgreSQL)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;Visual
RLS vs Masking
┌──────────────────────────────────────────────┐
│ TABLE: orders │
│ ┌────┬─────────────┬──────────┬────────────┐│
│ │ id │ customer_id │ product │ card ││
│ ├────┼─────────────┼──────────┼────────────┤│
│ │ 1 │ 1 │ Widget │ 4111... ││
│ │ 2 │ 1 │ Gadget │ 4222... ││
│ │ 3 │ 2 │ Widget │ 4333... ││
│ └────┴─────────────┴──────────┴────────────┘│
│ │
│ RLS (customer_id = 1): │
│ ┌────┬─────────────┬──────────┬────────────┐│
│ │ 1 │ 1 │ Widget │ 4111... ││
│ │ 2 │ 1 │ Gadget │ 4222... ││
│ └────┴─────────────┴──────────┴────────────┘│
│ Rows filtered, values real │
│ │
│ MASKING (card_number): │
│ ┌────┬─────────────┬──────────┬────────────┐│
│ │ 1 │ 1 │ Widget │ XXXX-1111 ││
│ │ 2 │ 1 │ Gadget │ XXXX-2222 ││
│ └────┴─────────────┴──────────┴────────────┘│
│ Rows visible, values masked │
│ │
│ BOTH: filtered rows + masked values │
└──────────────────────────────────────────────┘PostgreSQL RLS Policy
┌──────────────────────────────────────────────┐
│ ALTER TABLE orders ENABLE ROW LEVEL SECURITY│
│ │ │
│ ▼ │
│ CREATE POLICY p ON orders │
│ USING (customer_id = current_setting(...))│
│ │ │
│ ▼ │
│ Query: SELECT * FROM orders │
│ │ │
│ ▼ │
│ Policy evaluates per row │
│ customer_id = session value? │
│ │ │ │
│ yes no │
│ │ │ │
│ ▼ ▼ │
│ return row filter out │
└──────────────────────────────────────────────┘SQL Server RLS Components
┌──────────────────────────────────────────────┐
│ PREDICATE FUNCTION │
│ fn_Tenant(@TenantID int) → 1 or 0 │
│ │ │
│ ▼ │
│ SECURITY POLICY │
│ ADD FILTER PREDICATE fn_Tenant(TenantID) │
│ ON dbo.Orders │
│ │ │
│ ▼ │
│ User queries Orders │
│ │ │
│ ▼ │
│ Filter predicate runs per row │
│ Returns 1 → row visible │
│ Returns 0 → row hidden │
└──────────────────────────────────────────────┘Masking Function Output
┌──────────────────────────────────────────────┐
│ original masked (creditcard()) │
│ 4111-1111-1111-1111 → XXXX-XXXX-XXXX-1111 │
│ │
│ original masked (email()) │
│ alice@example.com → aXXX@XXXX.com │
│ │
│ original masked (partial(1,"XXX",0)) │
│ 555-123-4567 → 5XXXXXXX │
│ │
│ UNMASK permission → real value returned │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| RLS | Filters rows based on policy |
| Masking | Obfuscates column values in results |
| PostgreSQL RLS | CREATE POLICY, ENABLE ROW LEVEL SECURITY |
| SQL Server RLS | Predicate functions + security policies |
| SQL Server masking | MASKED WITH (FUNCTION = ...) |
| Unmask permission | GRANT UNMASK (SQL Server) |
| MySQL RLS | Not native; application-layer |
| MySQL masking | Enterprise Edition plugin |
| RLS bypass | BYPASSRLS (PostgreSQL), sysadmin (SQL Server) |
| Masking limitation | Inference attacks possible; not encryption |
Key takeaways:
- RLS and masking are independent controls. RLS hides rows; masking hides values within visible rows. Use both when both dimensions need protection .
- RLS is deny-by-default in PostgreSQL. Enabling it without a policy makes the table invisible to non-privileged users .
- SQL Server RLS uses predicate functions and security policies. Filter predicates hide rows; block predicates prevent unauthorized writes .
- Masking is native to SQL Server and Oracle, not PostgreSQL. PostgreSQL requires views, functions, or the
anonextension. - MySQL’s native RLS and masking are not available in Community Edition. Plan for application-layer or proxy-based enforcement.
- Masking is not encryption. It reduces casual exposure but does not prevent determined inference attacks. Use encryption for cryptographic protection .
- Superusers and table owners bypass these controls. Test with the actual non-privileged role that will use the system.
Remember: Row-level security and data masking are complementary layers in a database security stack. RLS enforces who can see which rows; masking controls what those rows look like when they are returned. PostgreSQL and SQL Server have strong native support for RLS; SQL Server and Oracle have native masking, while PostgreSQL relies on views and extensions. The implementation details vary by engine, but the principle is constant: push the enforcement as close to the data as possible, keep the policies simple enough to be performant, and test with the roles that will actually be subject to the controls. Neither control is a substitute for encryption when the threat model includes determined attackers with query access.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!