July 9, 2007 at 10:56 am
We have an ERP system called Munis, it's stored entirely in one DB. It requires a server user group that has all users and has the datareader role.
We want to create a second database that contains views that we have written against the Munis data that people can use Crystal Reports to query against. I just finished scrubbing the views so that SSNs are not displayed. The Crystal DB has only views pointing to the live database's tables. All objects in both the Munis DB and the Crystal DB are owned by DBO.
I'm having a problem getting security to work the way that we want it to, and I'll admit that my Security Fu is not strong. What we want is that people in the two network groups can read data from the views in the Crystal DB but cannot directly query the Munis data DB.
Munis uses an application server that all users connect to and are verified through. Then a single user ID is used for all processing in the SQL Server. This rather sucks, because from SQL Server, we can't audit exactly what people are doing. Fortunately Munis does some auditing, just not exactly what I would prefer.
So I'm kind of stuck. I'd like the users to not be able to update data when connected to the Crystal DB, plus they should not be able to browse the Munis DB's tables and query any table directly. I've been going back and forth on permissions and group memberships and such with pretty much no success, I'm hoping that someone can suggest structurally how I can do this. I think it's the server group that has datareader against the live database that's giving me fits, but I'm not certain.
Any help is greatly appreciated!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 10, 2007 at 3:32 am
I think you will need to have another user for reporting. This user will have:
1. read rights to the Crystal DB.
2. public rights to the Live DB with DENY SELECT on all the tables.
In order to avoid blocking your application, it would probably be best to have WITH (NOLOCK) in all your views.
You will need to test this as I would normally have a totally separate reporting db which is refreshed each evening.
July 10, 2007 at 5:10 pm
Thanks for the suggestions, Ken, I think it's on the right track. I've created a group on the server with my limited rights test account as the sole member at this time and permitted him as you suggested. I had to go in to the live side and give him select on the tables used in the views on the report side and now I can run the views.
I also modified all of my views with the NOLOCK, I'd missed that one!
I think I've got a problem, though, and I'll find out in the morning. I have a group of network users that have the roles datareader/datawriter to the live database. If I add them to my more restricted Crystal role, they'll still have datareader/datawriter, won't they?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 13, 2007 at 8:48 am
I have always had to grant permissions in the base database that was needed by the view database. May look into creating a role and grant permissions by column for the tables in the live database that are needed for the views to that role.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply