October 3, 2017 at 11:11 am
We have a handful of in-house database systems, where each db has tables within that specify what access each user has. So each stored proc uses these tables to filter what data a user can see or manipulate.
So one database may have a user who can see data related to three geographic regions and another database may have setting that limits the same user to one region. This works reasonably well but as the number of systems grows it becomes quite an overhead - updating every separate system as a user's settings are changed.
A new database and front-end is being written (let's call it 'UserLevel'), within which the settings for all of our other systems can be specified. So this one db contains details of which regions (as an example - there are more filters than just that) each user can access within each system.
I'm trying to work out the best way to pass this 'filter' information into each system, from this 'UserLevel' db into each separate database.
I don't particularly want to call a stored proc within 'UserLevel' from the other databases, because that means the databases will be effectively joined at the hip - and I have enough fun with synonyms and suchlike already.
The front-end for these other systems could (in theory) create a global temp table with each connection that the system's stored procs could interrogate but that sounds like a recipe for disaster.
I could have a parameter for every stored proc that insists the filter information retrieved from 'UserLevel' is supplied.
Or I could do something that is probably very obvious and I just haven't thought of.
Any helpful suggestions would be appreciated.
October 3, 2017 at 11:41 am
What's preventing you from devising a role based solution be it AD, Database, or Application roles in SQL Server?
October 4, 2017 at 12:51 am
HighPlainsDBA - Tuesday, October 3, 2017 11:41 AMWhat's preventing you from devising a role based solution be it AD, Database, or Application roles in SQL Server?
The number of possible permutations. To give a little more detail, this company owns a couple of hundred care homes, in about a dozen geographical areas and three countries. A user can be given access to either every care home in a country, every care home in one or more regions, or any number of specific care homes. To add to the fun, sometimes the care homes are re-organised, so they can be assigned to different regions, or merged or indeed other care homes may be purchased from other companies.
Trying to organise the different AD groups to cater for all of that would be a monumental task that I can't believe out Infrastructure people would agree to. And I don't fancy trying to create enough database or application roles to accommodate that either.
October 4, 2017 at 2:32 am
I don't normally get as far as actually posting the issue - usually writing it out brings a solution to light. The Rubber Duck.
October 4, 2017 at 9:24 am
Good luck!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply