2

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?

  • 1. Does your development / operations team prefer logic in the database or logic in the application? 2. Will there ever be any direct database connections, or is data only accessed through the application. – Nick.McDermaid Jul 11 '18 at 4:58
  • Thanks Nick. The owner isn't sure, wants best practice. There will never be a direct database connection I think. – Thor Russell Jul 11 '18 at 23:10
  • Best practice is not an absolute. It exists within requirements and constraints. TBH it's lazy to just say "gimme best practice". If you have no DB developers and no one ever connects directly to the database, then best practice is do it in the app. – Nick.McDermaid Jul 11 '18 at 23:28
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.