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
4
votes
1 answer

How to see the predicates added by a VPD in an Oracle database?

The predicates added by a Row Level Security policy do not show in the 10046 trace file. One suggestion is to use the 10053 (optimizer) trace to see the VPD predicates, but it is difficult to set the 10053 trace on a given session from another…
user2275479
  • 73
  • 1
  • 4
  • 8
4
votes
3 answers

Implementing Row-Level Security – (SPs vs LINQ to Objects)

I believe this is more a question about best practices and design than anything else. I tried searching for similar queries regarding this but couldn’t find any. I actually found the Row Level Security with Entity Framework but I believe the context…
Claiton Lovato
  • 2,382
  • 1
  • 22
  • 23
3
votes
1 answer

Why is the policy's "using"-clause used for the new row while a "with check"-clause is provided for an update statement?

From the Postgres CREATE POLICY documentation: UPDATE policies accept both a USING expression and a WITH CHECK expression. The USING expression determines which records the UPDATE command will see to operate against, while the WITH CHECK expression…
Christiaan Westerbeek
  • 10,619
  • 13
  • 64
  • 89
3
votes
2 answers

How to set run time variable to postgresql in typeorm and nest js

Iam using the row level security in supabase with nest.js, So how can I set runtime variables safely to the DB so that I can be sure that the variables sync with each app user (due to the http request triggered the execution)? I saw that it is…
Mohammed Shuhaib
  • 41
  • 1
  • 5
3
votes
0 answers

postgres optimisation: run view query before policy

I have a postgres table which has a policy enforced on it, like so (extra columns redacted for brevity): create table live_specs ( catalog_name catalog_name not null, spec_type catalog_spec_type not null, ); create policy…
Mahdi Dibaiee
  • 885
  • 1
  • 7
  • 20
3
votes
1 answer

can we insert or update data in table from RLS policy in Postgres?

I've created table "clientID_dbUser_mapping" which contains client id (e.g 1,2,3) and DB User name (u1,u2,u3) Now created another table "test_data" which contains id(PK), data(text), client_id(FK) created RLS policy for "test_data" to access the…
Lokesh1024
  • 115
  • 8
3
votes
1 answer

Row-level-security dual join

I have three tables journeys id user_id ... sections id journey_id ... stops id section_id ... I want to use row level security to make sure that a user can only insert a stop if the uid() matches the…
hoan
  • 1,277
  • 3
  • 18
  • 32
3
votes
0 answers

Replacing the IN operator in Power BI RLS

I want to ask if its possible to use something like IN operator in Power BI RLS DAX. The approach I am using now seems very convoluted to me, and I want to ask if it could be done better and simpler. I have two types of identifiers in my Fact table:…
Jixie
  • 76
  • 4
3
votes
1 answer

Unable to update row of table with Row Level Security

I'm trying to update a row in a table with row level security policies, but keep getting the error new row violates row-level security policy for table "my_table". Here's how I set up RLS policies: alter table my_table enable row level…
Denis Yakovenko
  • 3,241
  • 6
  • 48
  • 82
3
votes
1 answer

Postgres: auto-populating an `INSERT` field based on session variable

I have a web app backed by Postgres. Each web app request should only read/write data for the current logged-in user. Every table with user data has a user_id column. I occasionally have bugs where I forget to add user_id = ? to the WHERE clause…
Kannan Goundan
  • 4,962
  • 3
  • 24
  • 31
3
votes
1 answer

Postgres row level security insert returning

Given the following snippet from my schema: create table users ( id serial primary key, name text not null ); create table user_groups ( id serial primary key, name text not null ); create table user_user_group ( user_id …
daviestar
  • 4,531
  • 3
  • 29
  • 47
3
votes
1 answer

Simple SELECT query returns no result in Postgres database, depending on user

I am trying to create a read all user for my database, but it seems the most simplest of queries isn't working properly. When I try running the query select * from public.reports limit 5; it works fine when using the postgres user (db owner),…
Douglas Toledo
  • 33
  • 5
3
votes
1 answer

Cannot ALTER 'Table' because it is being referenced by object 'predicate' - SQL Server RLS with Temporal

I'm using Row-Level Security on a temporal table in my SQL Server database. In order to comply with GDPR, I need to be able to not only delete this data in this table that is from the present, but also its _History table (temporal table). Before…
user1054922
  • 2,101
  • 2
  • 23
  • 37
3
votes
1 answer

How to implement row level security (RLS) for raw sql access to amazon Redshift?

In the past I have implemented Row level security in Oracle and SQL Server databases using the security policies and predicate functions. Now my organization is moving to Amazon Redshift as the data warehouse, looking for inputs on ways to…
user11490966
  • 31
  • 4
3
votes
0 answers

Can I use RLS on a per user basis

I want to use PostgreSQL RLS capabilities to enable row level security in a database containing health sensitive data. Privacy by design is my main concern. Basically, I'm forced to connect to the database with a generic application user to benefit…
anon
1 2
3
…
24 25