SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


User filtering for several dbs from one 'security' db


User filtering for several dbs from one 'security' db

Author
Message
Steve Hall
Steve Hall
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10758 Visits: 12443
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.

Steve Hall
Linkedin
Blog Site
HighPlainsDBA
HighPlainsDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1004 Visits: 947
What's preventing you from devising a role based solution be it AD, Database, or Application roles in SQL Server?
Steve Hall
Steve Hall
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10758 Visits: 12443
HighPlainsDBA - Tuesday, October 3, 2017 11:41 AM
What'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.


Steve Hall
Linkedin
Blog Site
Steve Hall
Steve Hall
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10758 Visits: 12443

Got it.
When a user connects to one of these systems there is an initial call to the 'UserLevel' database to get the various access levels that the user has for that system. All I need to do is have the front-end then call a stored proc at that point, that writes these setting to tables within the target database - pretty much using the code and table structures that it has now.
You may not think you have helped out there but you have - writing the problem down and then saying why suggested solutions won't work helps me to work on the problem.

I don't normally get as far as actually posting the issue - usually writing it out brings a solution to light. The Rubber Duck.


Steve Hall
Linkedin
Blog Site
HighPlainsDBA
HighPlainsDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1004 Visits: 947
Good luck!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search