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

Row level security + Switch statement

I've a big table with lot of data who needs to have some RLS security. The RLS is based on an other table (user logins with a profile number). I've to make different logic of filtering depending on the profile number... Let's say if the userProfile…
Alexis
  • 5,681
  • 1
  • 27
  • 44
0
votes
0 answers

PowerBI RLS - Multiple View based on Roles

I am working on a PowerBI report which requires RLS. To simplify, let's say I have the following tables: Table 1: Sales, Account, ProductOwnerAccount, Owner, Region Table 2 Userid, UserRegion, UserAccount Scenario: Need to show two sets of tabular…
SKocheta
  • 133
  • 1
  • 8
0
votes
1 answer

SQLServer, temporarily disabling row level security

I have implemented row level security in SQLServer in Person table (to meet GDPR requirements) so that a basic user can see only subset of personal records. The Person table has some data (RFID tag) that must be unique in the system. So my app…
Heikki W
  • 1
  • 1
0
votes
1 answer

SQL Server row level security and Microsoft authentication

I know that SQL Server's RLS uses Windows Authentication, but is it possible to use external authentication? Like Google or Microsoft auth? I am doing testing on it using C# Web API 2 can't figure out how. Thanks in advance!
Jed
  • 1,054
  • 1
  • 15
  • 34
0
votes
0 answers

Record level security for multiple users in entity framework / Sql 2016

We needed to implement "who sees what" access in our application which is developed using Entity Framework 6.2. A simple problem statement is, we have three tables - User, Department and UserDepartments. In this one user can have access to…
Oxygen
  • 831
  • 4
  • 17
  • 42
0
votes
1 answer

revoke permission to execute as user on SA

MS SQL-SERVER 2016 I am currently using row level security on a database that has restrictions based on user name. This security protects rows on a table that stores application level settings - i.e. if you have bought it you can see it, if you are…
Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
0
votes
1 answer

Implement Row Level security column in inherited tables

I'm using Entity Framework with a code-first approach to generate the database. I have introduced row-Level security in SQL Server. I want to create 3 tables where all the common properties go to ItemVersion and document related properties to…
Aruna
  • 1,962
  • 4
  • 25
  • 50
0
votes
1 answer

Postgres RLS inconsistency on one table

I'm using RLS in a multi-tenancy model. No problems for the first several tables I enabled. Then I add RLS to a new table and suddenly I cannot insert a record to that table. Here is a table that works: CREATE TABLE wtr.adjustment ( id uuid…
Kevin Burkett
  • 1
0
votes
1 answer

Select with Row Level Security Settings

I'm attempting to add a select within the permission check for a postgres' row level security policy. I have 2 tables to care about here 1) group 2) group_member group - has a group_id and person_id (that is the person who made the group)…
Parris
  • 17,833
  • 17
  • 90
  • 133
0
votes
1 answer

Row Level Security with multiple users for one row

I'm trying to implement Row Level Security in SQL Server 2016. The problem is, I can have multiple users that should have read permissions over given rows, and when I write some complex condition in the predicate the performance gets like very very…
Stefan Taseski
  • 242
  • 2
  • 24
0
votes
1 answer

SQL Row-Level Security multiple users and for sysadmin

So, I was doing Row-level security policy when I noticed that when I apply it, it works as it should be for the users. But for me as sysadmin I can't see the table content anymore. Because the policy hides it from me, too. How can I add myself to…
mudstec
  • 3
  • 2
0
votes
1 answer

Apply filter and block predicate to a new table

I am working with a SQL Server database which has row-level security implemented. To access content for a certain user, I need to append this to the SQL query: EXEC SP_SET_SESSION_CONTEXT @key=N'UserId', @value=1 This works fine for existing tables…
Aditya Srivastava
  • 17
  • 2
  • 8
0
votes
0 answers

Enabing RLS and create policy. PostgreSQL 9.5

I have a problem while trying to enable rls for a login role I created. When I log in with sergio credintials the table doesn't respond.
Sergio Merdani
  • 1
  • 1
0
votes
1 answer

Can't create RLS on postgresql

I created a login role in Postgres then I tried to give permissions to modify specified rows on a table using row level security. I've tried thousands times but it doesn't work. I have only one table.
Sergio Merdani
  • 1
  • 1
0
votes
1 answer

grant in postgresql 9.5 specified for row

I want to add grant with where clause, not for columns, for different users. I'm using this grant update (ename), insert (empno, ename) on emp to xyz; for catching columns but I can't do this for rows. Is there any method ?
Sergio Merdani
  • 1
  • 1
1 2 3
…
24 25