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
41
votes
3 answers

Why isn't row level security enabled for Postgres views?

I need strict control of the reading and writing of my Postgres data. Updatable views have always provided very good, strict, control of the reading of my data and allows me to add valuable computed columns. With Postgres 9.5 row level security has…
Calebmer
  • 2,972
  • 6
  • 29
  • 36
15
votes
2 answers

how to implement row level security in spring data jpa using hibernate filter or other ways?

One of the very important problems in information softwares is the existence of users with different roles with different duties and access levels. For instance, think of an organization with the structure (hierarchy) like below: [Organization Role…
ali akbar azizkhani
  • 2,213
  • 5
  • 31
  • 48
14
votes
1 answer

PostgreSQL 9.5 - Row level security / ROLE best practices

I'm tying to grasp the best way to use the new row level security feature in a multi-tenant database that supports a web application. Currently, the application has a few different ROLEs available, depending on the action it is attempting to…
losthorse
  • 1,530
  • 1
  • 13
  • 33
12
votes
1 answer

Implementing Row Level Security in SQL Server 2008

Is there a built-in feature, or way to simulate RLS(Row Level Security) in SQL Server 2008 as found in Oracle?
TonyP
  • 5,655
  • 13
  • 60
  • 94
12
votes
1 answer

Row level security(RLS) performance is significantly slower in postgres.

Description : Here is the sample demonstration of the performance issue. We first created two tables , enabled row level security and created policy as well . Table definition: create table sample_schema.sample_table1(ID numeric(38) PRIMARY KEY NOT…
Nusrath Beevi
  • 133
  • 1
  • 1
  • 7
11
votes
1 answer

Combining row level security with column grants

Let's say I have a users table with three columns, public_data, private_data, and system_data, and I have three roles named postgres, authenticated_user, and visitor. postgres is superuser and can access all data (including system_data of…
Julien
  • 5,243
  • 4
  • 34
  • 35
11
votes
4 answers

Row Level Security with Entity Framework

I've been trying to consider how Row Level Security could be implemented with the Entity Framework. The idea is to have a database agnostic means that would offer methods to restrict the rows coming from the ObjectContext. Some of my inital ideas…
Rick
  • 722
  • 8
  • 17
10
votes
2 answers

How can you use 'For update skip locked' in postgres without locking rows in all tables used in the query?

When you want to use postgres's SELECT FOR UPDATE SKIP LOCKED functionality to ensure that two different users reading from a table and claiming tasks do not get blocked by each other and also do not get tasks already being read by another user: A…
Righto
  • 855
  • 3
  • 11
  • 32
10
votes
1 answer

PostgreSQL row-level security involving a view or a select with join

(suggestions for a better or more-descriptive title are welcome). I wonder if the following is possible in PostgreSQL using RLS (or any other mechanism). I want a user to be able to update certain rows of a table if its username matches a column in…
JayEye
  • 457
  • 5
  • 15
10
votes
1 answer

Row level security in SQL Server 2012

Is there something new about row-level security in SQL Server 2012? In 2008 and below the only way was using Views, in 2012 they announced something like Row Level Security in Tabular Models, does this have any relation to RLS over tables? Are there…
Shaddix
  • 5,901
  • 8
  • 45
  • 86
9
votes
3 answers

How to implement row-level security in Java?

I am currently evaluating authentication / authorization frameworks. Apache Shiro seems to be very nice but I am missing row-level security features. E.g. there might be special rows in a database which should only visible and accessible by users…
MRalwasser
  • 15,605
  • 15
  • 101
  • 147
8
votes
2 answers

How to optimize Row Level Security in Postgresql

I have postgres (13.2) based API with RLS enabled (I use postgraphile) and it's extremely slow. User sends JWT from Google OAuth. Access to tables are based on roles (there are 2: person, admin) + RLS. I have 2 tables for users auth: person,…
pszafer
  • 370
  • 2
  • 15
8
votes
6 answers

Why is Row Level Security (RLS) not using indexes?

I've got an application with Patients and Therapists. They're all in the same users table. Patients should be able to see their Therapist and Therapists should be able to see their Patients. I've set up a materialized view (user_access_pairs) with…
The Hoff
  • 904
  • 1
  • 11
  • 22
8
votes
1 answer

PostgreSQL ignores pg_trgm GIN indexes when Row Level Security is enabled and NOT LEAKPROOF operator is used

First of all, this SO question describes a similar problem: PostgreSQL query not using INDEX when RLS (Row Level Security) is enabled , but I was not able to successfully utilize it's suggestions and also would like to see if there is a way to…
user1762820
  • 223
  • 3
  • 8
8
votes
1 answer

Postgres Trigger side-effect is occurring out of order with row-level security select policy

Context I am using row-level security along with triggers to implement a pure SQL RBAC implementation. While doing so I encountered a weird behavior between INSERT triggers and SELECT row-level security policies. For simplicity the rest of this…
Carl Sverre
  • 1,139
  • 10
  • 19
1
2 3
…
24 25