SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


row/column-level security


row/column-level security

Author
Message
anders.lindell
anders.lindell
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 41
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
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6987 Visits: 4639
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
anders.lindell
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 41
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!
Jesse Reich
Jesse Reich
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 1023
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search