Small Changes and Big Impacts

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715078

    Comments posted to this topic are about the item Small Changes and Big Impacts

  • john.denniston

    SSC Enthusiast

    Points: 123

    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.


  • Eric M Russell

    SSC Guru

    Points: 124991

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715078

    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.

  • Eric M Russell

    SSC Guru

    Points: 124991

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715078

    Eric M Russell - Wednesday, March 28, 2018 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.

    RLS is intended to solve this without the multiple databases.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply