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 12»»

Only allow users to see their own records Expand / Collapse
Author
Message
Posted Sunday, March 24, 2013 8:36 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 929, Visits: 5,912
I'm working on the database from hell, so bear with me... (they think 'normalization' is a dirty word, or a foreign one...)

one of the rules for the users is that they can only see their own records. It's a little more complicated than that, but not a lot. The other fun part is the front end is Access 2013. Since Access can hold up to (I think) 2GB of data, I may have to upsize their backend to SQL Server 2012 Express, and then implement security. And then create stored procedures and views that the users can run, but deny all access to the underlying tables. Then each non-admin user can only see the "records" that are assigned to him. (I would link to or call the stored procedures from the front end, which would likely be Access).

If it matters, it's a Customer--buys--Product--from--Vendor database with a few minor tweaks.

It's a little more complicated than that - but not a lot. Each user is a salesperson assigned to Territory, so each one would be able see only the records that pertain to that territory. So I would just use SUSER_NAME() to filter the data, and it would filter by the username?

(Umm... did ya test it?)... does anybody know a good tutorial on basic SQL Server Security somewhere? I get the feeling that this is where the database may be going.

And one small digression - they're doing sales, so having maps of various territories and sites within them looks useful. What's the minimum version of SQL Server that I need for that? (Access Reports are just awful!).

Thanks!
Pieter
Post #1434732
Posted Monday, March 25, 2013 1:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:42 PM
Points: 1,310, Visits: 1,786
There's an example demonstration database called AdventureWorks2008 that sounds exactly like what you want to achieve in the long run.
http://msftdbprodsamples.codeplex.com/releases/view/55330

There's a tutorial on importing the data into mappoint to display it in reports etc.
http://msdn.microsoft.com/en-us/library/aa493431.aspx
Post #1434773
Posted Monday, March 25, 2013 9:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 4,472, Visits: 6,402
What you are looking to do is known as Row Level Security, and it is a certified BITCH to do in SQL Server. There is no easy/efficient way to do it. And it is certainly not something you can set up optimally based on some forum posts.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1434974
Posted Monday, March 25, 2013 1:54 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:59 AM
Points: 191, Visits: 904
It is certainly no easy task, but basically you want to create Views that restrict the rows that are returned, such as the following:

--SQL Server example
CREATE VIEW vw_MySales
AS
SELECT * FROM MySales WHERE TerritoryID IN (SELECT TerritoryID FROM AppUsers WHERE UserName = SUSER_SNAME())


This selects rows from the MySales records where the TerritoryID is in a list of territories (probably just one, since this is for a specific user, the one he is assigned to) that are returned from another select statement. This is if you want to show all records for a whole territory for the same territory the salesman (user) is assigned to, assuming he can see other salesmen's records in the same territory. You get the picture.

You can also create other views that restrict values, then use that view in the inside SELECT statement. This nesting gets very complicated very quickly. I had to do it this way, and it was a bear.

SPROCs are easier, because you can do more lookups and use other T-SQL code, a lot more code than VIEWs allow.

The key is denying their access to the underlying tables, and only allowing their use of the VIEWs and SPROCs.

This use of Table permissions would be very difficult in Access, much easier in SQL Server. You could upsize it in SQL Server, keeping an Access front-end.

Hope this helps more than confuses.....
Post #1435132
Posted Monday, March 25, 2013 3:28 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 929, Visits: 5,912
That's what I thought... I have no idea how else to do this besides RLS. (Thanks for the warning of how much fun it is... at least now I know!)

Looks like I wasn't that far off in my assessment, so now I feel better. I'll no doubt be back when I get the database normalized... (I hope you all are laughing, because I think it's hilarious... a drug manufacturer without a usable BI strategy!)
Post #1435174
Posted Monday, March 25, 2013 3:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
I wouldn't try using views, as then the table/view name changes every time, so you're forced to use dynamic SQL, which causes additional security headaches.

Instead, I suggest creating a controller table, which is *always* INNER JOIN'd to, that restricts the rows that are returned by any query.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1435179
Posted Monday, March 25, 2013 3:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Row level security is a nightmare on a well-designed database. Worked on a system once that used it, so much hassle, so much work required.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1435180
Posted Monday, March 25, 2013 3:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
I don't see why it would necessarily be that bad, at least for a straightforward "row must match the username" check. I've done these a few times w/o any major problems.

SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1435191
Posted Monday, March 25, 2013 3:51 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Depends how many tables you have to go through to get that column to filter on.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1435193
Posted Monday, March 25, 2013 4:34 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 929, Visits: 5,912
As I understand it (doesn't mean it's right), each salesperson is assigned to one or more geographic areas, and the customers are inside them, so it should be hierarchical. But if the state of their database is any indication, that may not be accurate.
Post #1435200
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse