What are the 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 than 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?
Here are the Pros and Cons for SQL server row-level security:
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 a 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 the application layer was filtering information through an ORM, you could debug and write auditing on the application or middle-ware side.
To use RLS or not? If your DB is well protected and access to data is through the 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.