March 26, 2018 at 8:01 pm
Comments posted to this topic are about the item Small Changes and Big Impacts
March 27, 2018 at 3:46 am
What I have done in old versions of SQL to control row-by-row access is to use a "Portal Database" pattern.
The actual data is held in a central database but users access it only through views in portal databases. Different users connect to different Portal databases and so only see the data in the views created for that database. Each view is tailored with an appropriate WHERE clause to only see the data in the actual table that is correct for their role. User access to any individual portal is controlled by the user's AD group as normal. It is generally possible to automate the creation of these views metadata.
March 27, 2018 at 8:01 am
Despite having a database developing career that spans 20 years and several significantly sized organization, I've somehow never had a use case for row level security, never even seen in, aside from stored procedures or views that implement filtering and role based security. It seems that the IS_ROLEMEMBER() function would come in handy.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 27, 2018 at 8:33 am
I've had the situation a couple of times where limiting access by row would work. This usually is a function of having customers that don't share data in any way, but are in groups of some sort. We have a few customers now that could use it, but there are challenges with redesigning databases to move data around.
March 28, 2018 at 8:42 am
I've worked with multi-tenant databases in the past, including situations where external clients had ad-hoc access query access. The way we dealt with it was to have a separate database for each client, so a hard boundary was established in the form of authentication when they logged in. But that did present maintenance issues like keeping all the schemas in sync and managing file growth and memory for 100s of individual tables. Eventually there was talk of having a single database, with a dedicated schema for each client, schema based authorization, and each clients data encrypted using a separate symmetric key. But that still would have presented the issue of how to keep schema DDL in sync.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 28, 2018 at 9:25 am
Eric M Russell - Wednesday, March 28, 2018 8:42 AMI've worked with multi-tenant databases in the past, including situations where external clients had ad-hoc access query access. The way we dealt with it was to have a separate database for each client, so a hard boundary was established in the form of authentication when they logged in. But that did present maintenance issues like keeping all the schemas in sync and managing file growth and memory for 100s of individual tables. Eventually there was talk of having a single database, with a dedicated schema for each client, schema based authorization, and each clients data encrypted using a separate symmetric key. But that still would have presented the issue of how to keep schema DDL in sync.
RLS is intended to solve this without the multiple databases.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply