In writing an article for SQL Server Standard's January issue, I received a tech edit comment from Adam Machanic
about a weakness in the typical row-level security implementation for
SQL Server that I wasn't aware of. Doing a little research (especially
using Adam's citations as MVPs Steve Kass and Sharon Dooley as
sources), I found a few references, especially in the newsgroups. There
was also this little gem from Microsoft titled Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005.
Using the traditional view-based security mechanisms allow for data to
be revealed when it shouldn't be. The big problem is "Predicative
Evalution Order" due to the way the Query Optimizer operates (and it's
the title of the section describing the issue in the Microsoft
article). The Query Optimizer has free reign to order the tables in any
way it needs to in order to present a reasonably optimized query plan.
An error in the query, such as a casting error, can cause things to
drop out and the error message itself will reveal some of the data.
This would potentially allow a savvy user to gain access to information
he or she would normally not be able to see.
All of it has been interesting to research and look into and a SQL
Server Central article should follow from it shortly. It's been about
six months since I've had time to write consistently but I'm hoping to
get back into a groove and write more often. Hopefully this subject
will be the one that kickstarts things.