ROW-LEVEL SECURITYSecurity
Buy Me A Coffee

Attach policies to a table so different users or roles automatically see or can modify only the rows they are permitted to access.

Automatically filters query results based on the current user identity, implemented in PostgreSQL (via policies) and SQL Server (via security policies). Critical for multi-tenant databases where rows must be isolated by tenant.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — Not supported natively. Must be simulated with views or application-layer filtering.
PostgreSQL ✓ Supported 9.5 ALTER TABLE ... ENABLE ROW LEVEL SECURITY, then CREATE POLICY. Policies can be permissive (OR logic) or restrictive (AND logic). Superusers bypass RLS by default; use FORCE ROW LEVEL SECURITY to override. session variables (current_setting()) are the standard way to pass the current user identity.
SQL Server ✓ Supported 2016 CREATE SECURITY POLICY using inline table-valued functions as filter and block predicates. More verbose than PostgreSQL. Supports FILTER predicates (hide rows) and BLOCK predicates (prevent writes).
Oracle ✓ Supported 8i Virtual Private Database (VPD) via DBMS_RLS.ADD_POLICY(). Predates PostgreSQL RLS. Requires PL/SQL — not pure SQL DDL. Policy function returns a WHERE clause string dynamically appended to queries.
SQLite ✗ Not Supported — Not supported. SQLite has no multi-user access control model.

Details

PostgreSQL's CREATE POLICY is the cleanest SQL DDL syntax. Oracle's VPD (Virtual Private Database) is the oldest implementation but requires PL/SQL. SQL Server's approach uses inline TVFs, which is powerful but verbose.

Standard Syntax

ALTER TABLE orders ENABLE ROW LEVEL SECURITY; CREATE POLICY own_orders ON orders USING (customer_id = current_setting('app.user_id')::int);

Version Support

MySQL: Not supported PostgreSQL: Since 9.5 SQL Server: Since 2016 Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

Not supported natively. Must be simulated with views or application-layer filtering.

-- Not supported. Workaround: a view with a WHERE clause CREATE VIEW my_orders AS SELECT * FROM orders WHERE customer_id = GET_USER_ID();

PostgreSQL

ALTER TABLE ... ENABLE ROW LEVEL SECURITY, then CREATE POLICY. Policies can be permissive (OR logic) or restrictive (AND logic). Superusers bypass RLS by default; use FORCE ROW LEVEL SECURITY to override. session variables (current_setting()) are the standard way to pass the current user identity.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY; ALTER TABLE orders FORCE ROW LEVEL SECURITY; -- Allow users to see only their own rows: CREATE POLICY own_orders ON orders FOR SELECT USING (customer_id = current_setting('app.user_id')::int); -- Allow admins to see everything: CREATE POLICY admin_all ON orders FOR ALL TO admin_role USING (true);

SQL Server

CREATE SECURITY POLICY using inline table-valued functions as filter and block predicates. More verbose than PostgreSQL. Supports FILTER predicates (hide rows) and BLOCK predicates (prevent writes).

CREATE FUNCTION dbo.rls_fn(@cust_id INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS ok WHERE @cust_id = CAST(SESSION_CONTEXT(N'user_id') AS INT); CREATE SECURITY POLICY dbo.OrderPolicy ADD FILTER PREDICATE dbo.rls_fn(customer_id) ON dbo.orders WITH (STATE = ON);

Oracle

Virtual Private Database (VPD) via DBMS_RLS.ADD_POLICY(). Predates PostgreSQL RLS. Requires PL/SQL — not pure SQL DDL. Policy function returns a WHERE clause string dynamically appended to queries.

CREATE FUNCTION rls_policy(schema_v VARCHAR2, tbl VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN 'customer_id = SYS_CONTEXT(''USERENV'',''SESSION_USER'')'; END; BEGIN DBMS_RLS.ADD_POLICY('SCOTT','ORDERS','own_orders','SCOTT','rls_policy'); END;

SQLite

Not supported. SQLite has no multi-user access control model.

-- Not supported.