Pros and cons of row level security Microsoft SQL Server What are the pros and cons of row level security?

We are porting a database from a desktop app to a web app and want to know the best way to make sure sensitive data is secured.

For our application there will be a limited number of users logging onto the system, e.g. trusted contractors rather then members of the public.

We could of course write our queries to check permissions on a case by case basis, or set up rows so that only contractors from that organization can see that row.

What criteria are used to decide what is best practice?
Pros

Row level security (RLS) reduces your application development complexity. Without RLS, you'd generally create filters with your queries or through ORM, or pass on filtering criteria to your procs. Once you have RLS in place, user1 sees what you desire user1 to see without changing your application much.

With RLS, security is at the DB level. If another application was reading data in a different language from a different platform, RLS still allows user1 to see what you want user1 to see. If someone is trying to run/test SQL queries to SSMS, RLS shows data according to your setup.

Your DB backup and restore will keep RLS intact.

Cons

The disadvantage is more DB-centric work and small amount of performance penalty. If you have web developers who aren't strong on the DB side, troubleshooting RLS might take them a bit of time. On the contrary, if application layer was filtering information through an ORM, you could debug and write auditing on the application or middle-ware side.

As far as the overheads of row level security go, here's an article that details some challenges: https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ and https://www.mssqltips.com/sqlservertip/4778/performance-impact-of-sql-server-2016-rowlevel-security/.

To use RLS or not?

If you are comfortable with RLS, I'd recommend you try it out. Microsoft has listed some RLS-related best practices here: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017.

If your DB is well protected and access to data is through application layer only, you can opt to build filtering within your application layer. It's not a lot of work to do security checks at the application layer AND use RLS. There isn't a one size fits all answer. For most simpler applications, I tend to use the application layer for filtering since it has served me well with auditing/logging/debugging.