October 19, 2007 at 8:30 am
Here is the deal. I am working on an international personnel system (hosted at the main office) and we want to keep people from one country from seeing data from another country. I am new to SQL 2005 and schemas, so I tried creating 2 employee tables one in the US schema and one in the UK schema. Then I created a view in the dbo schema vw_employees. I created 2 users us_test and uk_test with select rights in their respective schemas and select rights in the dbo schema. I had hoped that the schemas would cause the view to only show US data to the us_test user and UK data to the uk_test user, but when I ran a select on the view for either user I got all the data. Any ideas on how I could get the results I am looking for?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2007 at 10:11 am
I think you're getting rights to pass through. Why not have them select select from employees? Each individual will look for the table in their own schema only.
October 19, 2007 at 10:30 am
Their data can be in a single table.
Provide a view for each of them, but create the view "WITH CHECK OPTION", so they can only modify data that fits into their own view.
And document it ! So everybody seing the view's ddl, knows why it is being used.
Offcourse useraccess in this case should only go through these views and they should only be granted access to the view, not to tables.
So db_datareader/db_datawriter is a nono.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2007 at 10:51 am
Steve,
Thanks for the reply. I was under the assumption that by using union all from the 2 tables in the view that the schema permission would only allow the user to see the data from the table in the schema they have rights to. In the old owner world if the view was dbo and the tables had different owners the users would have to have rights to those tables. In this instance the UK user has no rights on the US table, but is getting to see the data through the view. I would have at least expected a permissions error.
The reason I went about it this way is that I want to have a single stored procedure say get_employees that would reference the view as I typically do not give direct rights to tables in any of my db's, I use sp's for all access. There is another thread, http://www.sqlservercentral.com/Forums/Topic410139-359-1.aspx that talks about schema issues within a stored procedure. I tried writing a stored procedure in the dbo schema and did not reference schema in my select, hoping it would resolve the user's default schema, but I get an error saying the table does not exist. So I tried the view route.
ALZDBA,
As you can see from above I was trying to limit access, yet have a single object everyone could access to see their data. I know I could also code based on role, but I wanted to be able to use SQL Server's built-in security model as much as possible.
If you have any suggestions let me know. I am still in the project planning and design phase so I don't have anything set in stone yet.
Also if anyone can point me to any good books\articles on SQL 2005 particularly on using schema's I'd love it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply