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 HallLinkedinBlog Site