row/column-level security

• anders.lindell

  SSC Veteran

  Points: 259

  More actions

  June 17, 2010 at 1:20 am

  #225775

  We have a Data Warehouse in SQL Server 2005 (soon to be 2008) with some confidential and sensitive information.

  Some users should only be allowed to see some certain Columns, e.g. Gross Profit, i.e. Column-level and some Regions should only see their data and not the other Regions, i.e. Row-level.

  I've been searching for this and it seems best to use a View setup for this.

  Does anyone have any best-practice, example or recommendations for this?

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 21, 2010 at 7:12 pm

  #1183054

  SQL Server offers RLS/CLS - short for Row Level Security / Cell Level Security. As I understand it RLS/CLS offers similar funtionality of what is called Virtual Private Database in other technologies.

  Check it here... http://technet.microsoft.com/en-us/library/cc966395.aspx

  Hope this helps.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• anders.lindell

  SSC Veteran

  Points: 259

  More actions

  June 21, 2010 at 11:30 pm

  #1183077

  Pablo thanks for your response. Yes, it is something similiar to virtual private db I'm looking for, for SQL Server 😎

  This link I've been searching for and haven't found it, I'm very grateful!

• Jesse Reich

  Hall of Fame

  Points: 3020

  More actions

  June 23, 2010 at 11:51 am

  #1184120

  Last year I wrote a white paper for a project about row-level security. The info in the document is proprietary to one of our products but I do have all the references I used.

  One of the reasons you will see so many links to Oracle information is because Oracle has row-level security built in. I learned a great deal about best-practices and typical usage from studying their documentation. I would definitely recommend reading their introductions to row-level security (typically the first chaper of the administrator's guide). There is a great deal of info on how its used in the marketplace.

  Anyway, the links are below:

  Berkus, Josh. “Thinking about Row Level Security” (2009):

  http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-1-30732

  Davidson, Louis. “Pro SQL Server 2008 Relational Database Design and Implementation” (2008):

  http://books.google.com/books?id=ekEt972gEDIC&pg=PT442&dq=louis+davidson+is_member+row+level+security&cd=2#v=onepage&q=&f=false

  Erdogan, Kemal. “A Fairly Capable Authorization Sub-System with Row-Level Security Capabilities (AFCAS)” (2008): http://www.codeproject.com/KB/database/AFCAS.aspx

  Finnigan, Pete. “Oracle Row Level Security” (2003): http://www.securityfocus.com/infocus/1743

  Finnigan, Pete. “Using Oracle VPD in the Real World” (2008): http://www.petefinnigan.com/Oracle_Security_VPD6Slides.pdf

  Kondreddi, Narayana Vyas. “Implementing row level security in SQL Server databases” (2001): http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

  Lambert, Bob. “Protecting Your Data with Row Level Security for SQL Server Databases” (2009): http://www.ddj.com/database/215900773;jsessionid=HXW3NHLZHKL4FQE1GHOSKHWATMY32JVN?pgno=1

  Lewis, Jonathan. “Row Level Security” (2006):

  http://www.dbazine.com/oracle/or-articles/jlewis15

  Marston, Tony. “A Role-Based Access Control (RBAC) system for PHP” (2004): http://www.tonymarston.net/php-mysql/role-based-access-control.html

  Microsoft Corporation. “BUG: Changes to the Group Membership in Windows Are Not Reflected Immediately in the SQL Server IS_MEMBER Function” (2009): http://support.microsoft.com/kb/812774

  Oracle Corporation. “Oracle Label Security Administrator’s Guide 10g Release 1 (10.1)” (2003): http://download.oracle.com/docs/cd/B19306_01/network.102/b14267.pdf

  Oracle Corporation. “Oracle Label Security Administrator’s Guide 11g Release 1 (11.1)” (2007): http://download.oracle.com/docs/cd/B28359_01/network.111/b28529.pdf

  Oracle Corporation. “Oracle Label Security in Government and Defense Environments” (2009):

  http://www.oracle.com/database/docs/database-govdef-label-security-whitepaper.pdf

  Rask, Art et al. “Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005” (2005):

  http://msdn.microsoft.com/en-us/library/cc966395.aspx