Stack Overflow
Stack Overflow

Questions tagged [row-level-security]

Row-level security is database security term which relates to the ability to define and enforce access control logic on rows of data such that a user can only retrieve the rows of data he or she is allowed to view.

Row-level security is database security term which relates to the ability to define and enforce access control logic on rows of data such that a user can only retrieve the rows of data he or she is allowed to view.

Several database vendors provide row-level security mechanisms. For instance:

  • Oracle provides Virtual Private Database (VPD), a free feature of the Oracle Enterprise Database.
  • MySQL provides fine-grained access control (FGAC). This is further detailed in this 2006 article.
  • IBM DB2 provides row/column-level access control (RCAC). This is further detailed in this IBM knowledge base.
  • SQL Server and Azure SQL Database provide Row-Level Security (RLS). This is further detailed in the MSDN product documentation.

In row-level security, a user can ask to view a set of data e.g. medical records. The database table (or view) contains a complete set of medical records but only returns those records the user is entitled to view. The authorization is typically driven through the configuration of VPD/RCAC/FGAC or through an access control policy e.g. doctors can view the medical records of patients they are assigned to.

Row-level security is becoming more prevalent with the rise of and , technologies that help standardize access control.

An extension of row-level security is the ability to apply cell-level security. This space has been coined as dynamic data masking by Gartner analyst Joseph Feiman (see this report and these videos on data masking).

There are several third party vendor solutions which provide row-level security / dynamic data masking:

  • GreenSQL
  • Informatica DDM
  • Axiomatics Data Access Filter MD

Additional information and vendors are listed on Wikipedia.

367 questions
8
votes
2 answers

PostgreSQL query not using INDEX when RLS (Row Level Security) is enabled

I am using PostgreSQL 10.1, going right to the point... Lets say I have a TABLE: CREATE TABLE public.document ( id uuid PRIMARY KEY, title text, content text NOT NULL ); Together with a GIN INDEX on it: CREATE INDEX document_idx ON…
enisdenjo
  • 706
  • 9
  • 21
8
votes
2 answers

Entity Framework - Setting session_context using IDbConnectionInterceptor

I'm following this tutorial in order to use Row Level security in SQL Server via Entity Framework 6 CodeFirst. The tutorial code sample shows how to use IDbConnectionInterceptor and set the current user id in session_context. To retrieve the user…
ravinsp
  • 4,150
  • 2
  • 32
  • 43
7
votes
2 answers

How to check if row level security is enabled for a table in postgres

Enabling row level security on a table in postgres is pretty straightforward: alter table some_table enable row level security; How would you check to see which tables in a given schema have row level security enabled (for testing)?
Oliver Rice
  • 768
  • 8
  • 20
7
votes
2 answers

Row level security for groups or Making rows accebile to groups

I want the rows in a table accessible to only members of groups. I create users and add them to group by following method, CREATE USER abc LOGIN PASSWORD 'securedpassword1'; CREATE USER xyz LOGIN PASSWORD 'securedpassword2'; ALTER GROUP permanent…
khaldi
  • 472
  • 7
  • 15
7
votes
1 answer

Row Level Security in Postgres on Normalized Tables

The premise In documentation, Row Level Security seems great. Based on what I've read I can now stop creating views like this: SELECT data.* FROM data JOIN user_data ON data.id = user_data.data_id AND user_data.role = CURRENT_ROLE The great part…
cazzer
  • 1,726
  • 2
  • 18
  • 29
7
votes
3 answers

Row-level security in a client-database scenario

I am looking for a good pattern to implement row-level security controls (via e.g. a proxy, man-in-the-middle web service, or stored procedures) suitable for use in a client->database environment. I control both the client and the database. Some…
Drew
  • 8,675
  • 6
  • 43
  • 41
5
votes
2 answers

Only allow read if user has exact document id postgresql row level security/supabase

Is there a way for a user to only be able to read a document only if they have the exact document ID? I want to avoid creating users, so the only security is a random guid saved in browser memory - settings will be saved in "settings" table with…
Rune Jeppesen
  • 1,121
  • 13
  • 22
5
votes
2 answers

How to compare between old and new values of a row in postgres policy for update

I have a permission structure so that a specific permission only allows to edit 2 out of 5 fields in my table. I have RLS in my entire system so I need to execute the above inside the policy. At first I thought of writing a function that checks if…
Amit Toren
  • 351
  • 3
  • 13
5
votes
0 answers

Row Level Security Query Plan is 45x slower than identical non-RLS query

I'm having some trouble getting the query planner to write good plans for row level security (RLS) enabled tables. It seems all it takes is a join from a row level security enabled table to a non-row level security enabled table to force a bad plan…
Justin
  • 500
  • 8
  • 18
5
votes
1 answer

In Oracle VPD / RLS, how are malicious user predicates prevented from leaking info?

I've been reading the docs for Oracle VPD (Virtual Private Database, a.k.a. fine-grained security, the basis of label-based security), and there's something I'm having a hard time grasping. How does VPD prevent a user from leaking information using…
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
4
votes
1 answer

INSERT statement returns a policy violation (USING expression)

I am using AWS Aurora Postgres 14 (14.5) and have this table set-up (with the help of Diesel in Rust) -> create table contacts ( email TEXT NOT NULL, user_id TEXT NOT NULL, contact_data JSONB, user_groups TEXT[], tenant_groups…
parmesant
  • 73
  • 4
4
votes
1 answer

PostgreSQL Row Security Policies is not working for CREATE POLICY FOR UPDATE WITH CHECK (false);

I'm trying to set up row level security on some tables. By now I had no issues with INSERT and SELECT, now trying to handle UPDATE. I tried many things but really got stuck when I defined the policy with WITH CHECK (false) and I have the ability to…
noam steiner
  • 4,034
  • 3
  • 27
  • 42
4
votes
0 answers

For node-postgres ("pg" library), are session variables safe doing Row-level-security?

I'm making a website with multiple login users and I want to setup row-level-security so that my app can scale. I have a lot of many-many relations with access defined via join-tables. I don't want to have to append each db query with a very long…
user9157769
4
votes
2 answers

Row level security using prisma and postgres

I am using prisma and yoga graphql servers with a postgres DB. I want to implement authorization for my graphql queries. I saw solutions like graphql-shield that solve column level security nicely - meaning I can define a permission and according to…
brafdlog
  • 2,642
  • 1
  • 18
  • 21
4
votes
0 answers

After Update errors on Memory optimized tables SqlServer

Using EF 6.1.3, SqlServer 2016. I have recently modified certain tables in my DB to memory optimized tables. Everything functions correctly, however, when I added an after update trigger on a non-memory optimized table I get the following error. SQL…
David Moseby
  • 41
  • 2
1
2
3
…
24 25