row/column-level security

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » row/column-level security

row/column-level security

Rate Topic

Display Mode

Topic Options

Author

Message

anders.lindell

Posted Thursday, June 17, 2010 1:20 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:17 AM
Points: 17, Visits: 36

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?

Post #938689

PaulB-TheOneAndOnly

Posted Monday, June 21, 2010 7:12 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:03 AM
Points: 2,979, Visits: 4,389

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.

Post #940714

anders.lindell

Posted Monday, June 21, 2010 11:30 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:17 AM
Points: 17, Visits: 36

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

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

Post #940753

Jesse Reich

Posted Wednesday, June 23, 2010 11:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 6:19 PM
Points: 164, Visits: 894

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

Post #941971

« Prev Topic | Next Topic »

Permissions