Can my DB?

-- Enable RLS on a table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owner
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Permissive SELECT policy (rows visible to owner)
CREATE POLICY orders_select_own
  ON orders
  AS PERMISSIVE
  FOR SELECT
  TO app_user
  USING (user_id = current_user_id());

-- Restrictive policy using a setting
CREATE POLICY orders_tenant
  ON orders
  AS RESTRICTIVE
  FOR ALL
  TO app_role
  USING (tenant_id = current_setting('app.tenant_id')::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

-- Drop a policy
DROP POLICY orders_select_own ON orders;

• Superusers and table owners bypass RLS by default; use ALTER TABLE t FORCE ROW LEVEL SECURITY to apply policies to owners.
• PERMISSIVE policies are combined with OR; RESTRICTIVE policies are ANDed with the combined permissive result.
• USING clause controls which rows are visible (SELECT, UPDATE, DELETE); WITH CHECK controls which rows can be written (INSERT, UPDATE).
• current_user and current_setting() are commonly used in policy conditions for multi-tenant applications.
• RLS policies are transparent to the application; the WHERE clause is injected automatically.

-- No native RLS in MySQL.
-- Common workaround: a view with a WHERE clause filtering by CURRENT_USER()
CREATE VIEW orders_view AS
  SELECT * FROM orders
  WHERE user_id = (SELECT id FROM users WHERE username = CURRENT_USER());

-- Grant access to the view, not the base table
GRANT SELECT ON orders_view TO 'app_user'@'%';

• MySQL has no native row-level security mechanism.
• The standard workaround is to create a definer-rights view with a WHERE clause and grant access to the view instead of the base table.
• View-based RLS cannot transparently enforce per-row policies for writes (INSERT/UPDATE/DELETE).
• Application-layer access control is often used as a complement or alternative.

-- No native RLS in MariaDB.
-- Same view-based workaround as MySQL
CREATE VIEW orders_view AS
  SELECT * FROM orders
  WHERE user_id = (SELECT id FROM users WHERE username = CURRENT_USER());

GRANT SELECT ON orders_view TO 'app_user'@'%';

• MariaDB does not have native row-level security.
• The view-based workaround (same as MySQL) is the most common approach.
• MariaDB role system can be combined with views for coarser-grained access control.
• No equivalent to PostgreSQL CREATE POLICY or Oracle VPD.

-- Create a policy function that returns a WHERE predicate string
CREATE OR REPLACE FUNCTION orders_vpd_policy(
  schema_name IN VARCHAR2,
  table_name  IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN 'user_id = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
END;
/

-- Add the policy to the table
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'MYSCHEMA',
    object_name     => 'ORDERS',
    policy_name     => 'ORDERS_USER_POLICY',
    function_schema => 'MYSCHEMA',
    policy_function => 'ORDERS_VPD_POLICY',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE'
  );
END;
/

-- Remove a policy
BEGIN
  DBMS_RLS.DROP_POLICY('MYSCHEMA', 'ORDERS', 'ORDERS_USER_POLICY');
END;
/

• Virtual Private Database (VPD) is Oracle's row-level security implementation, available since Oracle 8i.
• The policy function returns a VARCHAR2 string that is appended as a WHERE predicate to every qualifying SQL statement.
• VPD policies are invisible to the application; the predicate is injected transparently by the optimizer.
• Supports static (evaluated once per session), dynamic (evaluated per statement), and context-sensitive policy types.
• SYS_CONTEXT('USERENV', ...) provides application context values for use in predicates.
• Requires the EXECUTE privilege on DBMS_RLS.

-- Create an inline table-valued function as the security predicate
CREATE FUNCTION Security.fn_orders_filter(@UserID int)
  RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN SELECT 1 AS fn_result
  WHERE @UserID = CAST(SESSION_CONTEXT(N'UserID') AS int);
GO

-- Create a security policy using a filter predicate (restricts SELECT)
CREATE SECURITY POLICY OrdersPolicy
  ADD FILTER PREDICATE Security.fn_orders_filter(user_id) ON dbo.Orders,
  ADD BLOCK PREDICATE Security.fn_orders_filter(user_id) ON dbo.Orders
  WITH (STATE = ON);
GO

-- Disable the policy temporarily
ALTER SECURITY POLICY OrdersPolicy WITH (STATE = OFF);
GO

• Row-Level Security was introduced in SQL Server 2016 and Azure SQL Database.
• Filter predicates restrict rows returned by SELECT, UPDATE, and DELETE.
• Block predicates restrict rows from being inserted or updated to values that violate the predicate.
• The predicate function must be an inline table-valued function (iTVF) with SCHEMABINDING.
• SESSION_CONTEXT() is commonly used to pass application-level user context into the predicate.
• Security policies can be enabled or disabled without dropping them.

-- SQLite has no RLS mechanism.
-- Use a view to approximate read-only row filtering
-- (SQLite has no concept of database users at runtime)
CREATE VIEW orders_view AS
  SELECT * FROM orders WHERE status = 'public';

-- Application-layer filtering is the standard approach
SELECT * FROM orders WHERE user_id = ?;

• SQLite has no multi-user access control or row-level security mechanism.
• SQLite is typically embedded and relies entirely on the application for access control.
• Views can approximate read filtering but cannot enforce write restrictions.
• For multi-user scenarios, consider a client-server database.

-- Create an RLS policy
CREATE RLS POLICY orders_user_policy
  WITH (user_id int)
  USING (user_id = current_user_id());

-- Attach the policy to a table for a role
ATTACH RLS POLICY orders_user_policy
  ON orders
  TO ROLE analyst_role;

-- Enable RLS on the table
ALTER TABLE orders ROW LEVEL SECURITY ON;

-- Detach a policy
DETACH RLS POLICY orders_user_policy ON orders FROM ROLE analyst_role;

-- Drop a policy
DROP RLS POLICY orders_user_policy;

• Redshift RLS was introduced in late 2022.
• Policies are defined with a USING condition and attached to tables for specific database roles or users.
• The policy column types must match the table column types.
• Superusers and users with the IGNORE RLS privilege bypass row-level security.
• current_user_id() and other context functions can be used in policy conditions.
• IAM roles can be mapped to database roles for AWS-integrated access control.

-- Create a row permission
CREATE OR REPLACE PERMISSION orders_row_access
  ON orders
  FOR ROWS WHERE
    VERIFY_ROLE_FOR_USER(SESSION_USER, 'ADMIN') = 1
    OR user_id = SESSION_USER
  ENFORCED FOR ALL ACCESS
  ENABLE;

-- Activate row access control on the table
ALTER TABLE orders ACTIVATE ROW ACCESS CONTROL;

-- Disable a permission (without dropping)
ALTER PERMISSION orders_row_access DISABLE;

-- Drop a permission
DROP PERMISSION orders_row_access;

• DB2's Row and Column Access Control (RCAC) is available since DB2 9.7.
• CREATE ROW PERMISSION defines a predicate; only rows matching the condition are accessible.
• Multiple permissions on the same table are combined with OR.
• SESSION_USER and VERIFY_ROLE_FOR_USER() are used to reference the current user and role.
• RCAC also supports column-level masking (CREATE MASK) for data redaction.
• Setup is more complex than PostgreSQL RLS and requires activating access control on each table separately.
• Despite the heavier setup, RCAC is native database-enforced row-level security.

-- Create a row access policy (returns boolean)
CREATE OR REPLACE ROW ACCESS POLICY orders_rap
  AS (user_id VARCHAR) RETURNS BOOLEAN ->
    CURRENT_ROLE() IN ('ADMIN', 'DATA_ENGINEER')
    OR user_id = CURRENT_USER();

-- Attach the policy to a table column
ALTER TABLE orders
  ADD ROW ACCESS POLICY orders_rap ON (user_id);

-- View policies attached to a table
SELECT * FROM information_schema.policy_references
  WHERE ref_entity_name = 'ORDERS';

-- Detach a policy
ALTER TABLE orders
  DROP ROW ACCESS POLICY orders_rap;

• Snowflake Row Access Policies are inline functions that return a boolean, applied to a specific column.
• The policy is attached to a column rather than the table itself; that column value is passed to the function.
• CURRENT_USER(), CURRENT_ROLE(), and IS_ROLE_IN_SESSION() are commonly used in policy conditions.
• Policies are schema-level objects and can be reused across multiple tables.
• Only one row access policy can be attached to a table at a time.
• The Data Governance worksheet in Snowsight makes it easy to manage and audit policies.

-- Create a row access policy granting access to specific principals
CREATE ROW ACCESS POLICY orders_rap
  ON mydataset.orders
  GRANT TO ('user:alice@example.com', 'group:analysts@example.com')
  FILTER USING (region = 'US');

-- Create a policy referencing the current user
CREATE ROW ACCESS POLICY orders_owner_policy
  ON mydataset.orders
  GRANT TO ('user:bob@example.com')
  FILTER USING (owner_email = SESSION_USER());

-- Drop a row access policy
DROP ROW ACCESS POLICY orders_rap ON mydataset.orders;

-- Drop all policies on a table
DROP ALL ROW ACCESS POLICIES ON mydataset.orders;

• BigQuery row-level security is enforced through row access policies integrated with IAM.
• A policy GRANTS access to specific users or groups for rows matching the FILTER USING condition.
• Rows not matching any policy for a given user are invisible to that user.
• Users with Fine-Grained Reader permission on the table see all rows.
• SESSION_USER() returns the email of the currently authenticated user.
• Multiple policies on the same table are combined with OR; a user sees rows matched by any policy they belong to.
• Policies are compatible with column-level security (column policy tags).