Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

row/column-level security Expand / Collapse
Author
Message
Posted Thursday, June 17, 2010 1:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:20 AM
Points: 17, Visits: 40
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
Posted Monday, June 21, 2010 7:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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
Posted Monday, June 21, 2010 11:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 8:20 AM
Points: 17, Visits: 40
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!
Post #940753
Posted Wednesday, June 23, 2010 11:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:18 PM
Points: 164, Visits: 962
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 »

Add to briefcase

Permissions Expand / Collapse