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

How to check tables which are having Row access policy in Snowflake

I have created few tables in a schema and a row access policy with "create or replace row access policy .." command. Then I have added the row access policy to the tables with "alter table add row access policy…
jsrathnayake
  • 139
  • 10
2
votes
1 answer

How to reference query values in Postgres res policy

I am trying to build a system with Postgres in which users can connect with each other by sending requests. I am handling the security logic with RLS. However, I am having some trouble with the structure of one of my policies. Here are the tables of…
Max
  • 754
  • 8
  • 24
2
votes
1 answer

Designing a Multi-tenant SAAS Database with Postgres RLS

I want to design a multi-tenant SAAS database with PostgreSQL and RLS. I want to be able to host all users and the tenants in the same database and isolate their data with RLS. In my use case it makes sense for tenants to share a certain user data…
Ghost
  • 45
  • 1
  • 8
2
votes
2 answers

Row-level-security based on relation table data

I am getting into Supabase and to practice I am making a suuuper simplified website-builder. However I am having troubles with the row-level-security policies. I have three tables: user → with users' information like first name, last name,…
st_phan
  • 715
  • 9
  • 23
2
votes
1 answer

What are the best practices for advanced row-level security in SSAS tabular models?

I am currently building up a new data model in the area of sales. In the old data model, which I want to replace, I had very specific row-level security settings for each user: Sometimes, settings can be done with an "AND" filter: UserXY should…
Ivo
  • 303
  • 2
  • 15
2
votes
1 answer

Are RLS policy definitions affected at runtime by the search_path?

While answering this question, I gave some (unfounded) advice on create policy test_policy on policy for all to public using ( user_id = session_user_id()); Btw, you should schema-qualify the session_user_id() call to make your policy…
Bergi
  • 630,263
  • 148
  • 957
  • 1,375
2
votes
1 answer

Postgres RLS policy: Only allow SELECT on row based on existence of foreign relation

I have the following Postgres tables: users +---------+--------------+ | id (PK) | full_name | +---------+--------------+ | uuid() | varchar | +---------+--------------+ organizations +---------+---------+ | id (PK) | name …
tdc
  • 5,174
  • 12
  • 53
  • 102
2
votes
3 answers

Achieve Row Level Security(RLS) on Snowflake by assigning Secure views to roles and provide filter on multiple conditions

Let's say we have a table Fruits details, Country Fruit USA Apple India Mango Italy Kiwi Australia Guava We have 3 Roles i.e. region1_role, region2_role and global_role. I want region1_role to have access to both USA and…
Mohammed Turky
  • 96
  • 7
2
votes
1 answer

Using PostgreSQL row level security (RLS) policies with current_setting() function

I've applied RLS policy to the "users" table and expect only records with tenant_id=2 to be retrieve: CREATE TABLE "users" ("name" text UNIQUE NOT NULL, "tenant_id" int NOT NULL DEFAULT current_setting('app.current_tenant')::int); --Enable Row…
Yuri Kuschinsky
  • 23
  • 4
2
votes
1 answer

Row-level security for multiple roles for hierarchy in PostgreSQL

I'm trying to implement row-level security in Postgres. In reality, I have many roles, but for the sake of this question, there are four roles: executive, director, manager, and junior. I have a table that looks like this: SELECT * FROM…
smulard
  • 139
  • 6
2
votes
1 answer

How to implement row level security for a multi-tenant SAAS app

After reviewing the different options for creating a multi-tenant database architecture, I have decided to use the 'Single database and same tables for all client, but we have tenant_id in all tables, so we query the data according to specific…
Jake
  • 3,865
  • 5
  • 25
  • 58
2
votes
2 answers

How to apply dynamic Row level Security based on Username and location?

This is related to Row-level security in Power BI. Here is a dummy table on which I want to implement this RLS. The RLS formula I am using right now is - [location] = LOOKUPVALUE([location],[login],USERPRINCIPALNAME()) My end requirement is to…
Deepesh Vashistha
  • 21
  • 1
  • 2
2
votes
1 answer

Row level security only on SELECT

I am enabling Row level security on a table. I want the row security only on SELECT. So that other users can insert and update their respective records. I am using postgreSQL and I am pretty much beginner for 'ROW-LEVEL-SECURITY'. I am trying it…
khaldi
  • 472
  • 7
  • 15
2
votes
1 answer

security for some users to only read the record which is active (flag 1 or latest date)

In a DataWarehouse (SQL Server 2012) and for some table(s) I want to implement an option where some users can only see the latest row (date or flag) instead of the whole history. Is this possible?
FrankieJ
  • 21
  • 1
2
votes
2 answers

Zend_Db_Select row level security

How could I accomplish row level security using Zend_Db_Select? I can think of a few options, but they don't really seem to fit the pattern quite right. Let's say I have users, content, and many different ACL levels. Here's one solution I've thought…
Stephen Fuhry
  • 12,624
  • 6
  • 56
  • 55
1 2 3
…
24 25