March 24, 2013 at 8:36 pm
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
March 25, 2013 at 1:53 am
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.
March 25, 2013 at 9:16 am
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 on googles mail service
March 25, 2013 at 1:54 pm
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.....
March 25, 2013 at 3:28 pm
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!)
March 25, 2013 at 3:38 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 25, 2013 at 3:38 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2013 at 3:48 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 25, 2013 at 3:51 pm
Depends how many tables you have to go through to get that column to filter on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2013 at 4:34 pm
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.
March 25, 2013 at 6:52 pm
Just had a client hit me up today with a need for RLS - with a twist. The "owner" of the record always has access and that owner can allow the row to be readable to all users OR restrict access to just a list of users. My first thought - "kaaCHIINNNGGG"!! 😎 (Un)fortunately it was just for one table and I quickly hashed out a construct for them to prototype based on past experiences with RLS.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 26, 2013 at 7:33 am
I work on a database where our clients log on to our database and we want them to ONLY see their specific SQL data. That is done via a Pass Through Access query created in code (the Pass Through query varies depending on the login). Access make that fairly easy IF you are capable of programming it (in VBA for example).
March 26, 2013 at 9:01 am
This sounds like something better handled at the application level. An application like DotNetNuke (which is free BTW) is well-developed for securely handling individual or group roles. Then if any customization is necessary--which my business is based on--all procedures or views are written to allow access based on the user's unique id or a specific role or group they belong to.
End users shouldn't be accessing the database anyway except through a secure front-end GUI of some sort.
March 26, 2013 at 9:24 am
Might do that... they have Access and VBA is easy... forgot about doing pass-through queries... Makes a nice front end, but not my favorite as a backend.
March 26, 2013 at 9:28 am
..and you can control access via a password (again, VBA)..if users are smart, they may know re: SHIFT as a BYPASS in Access - that can also be turned on and off via code and passwords
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply