Give access to users for certain data in the table or move that set of data to new table

Question

Give access to users for certain data in the table or move that set of data to new table

BoobyB

SSCertifiable

Points: 5470
More actions
August 8, 2017 at 8:25 am

#344062

Hi All,
I have a question for the senior members to get a solution forthe below issue.
Issue:

We have a database, and in that particular DB we have twolarge tables that have clients/customers information details. These customersbelong to different region (let’s say some in NY, some in NJ and some in CT).
Currently, the city wide users have access to the complete tableand they can see data from all the states. Create a plan so each city user needs to haveaccess to data related to their own city. Either make change on the current DBor create a new table in a new DB and only has data that is related to particularcity.
It is an OLAP DB so data gets loaded every evening on everyday. The user gets access the day next morning after the successful load in theprevious evening.
Needs to present all the solutions available in SQL Server, Ihave come up with the following ones and would like to have a feedback if anyother alternatives available. Pros and cons for each option.

-         Create an ETL process and load the data in thenew table suing the where clause.
-         Create a view, and give client access to theview to see data only for the particular city in the table.
-          In SQLSERVER 2016 row level security can be implemented. However this is only availablein 2016 as far as I know.
-         Replication – but this is an OLAP env and notmuch transactional changes happening once the evening load is finished (snapshotreplications).

Any other options?

Viewing 5 posts - 1 through 4 (of 4 total)

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

BoobyB SSCertifiable Points: 5470 More actions · Answer 1

Hi Guys,

Any input in this matter in appreciated. I forgot to mention that a simple select into can also provide a solution if moving to a different/new table.

Regards,
B

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Depending on how they need to share the data, you could have a new database per city.
likely not an ideal solution, but it is another option.

a new sql instance for each region. You could have a stored procedure return the data to the end users based on their region.

The above are not "the best" solutions, just alternate solutions. Without knowing how the end users will consume the data and how the data will be interacted with and how secure the data needs to be, it is difficult to say what is the best solution. It's like asking "what is the best car?". Is it a fast sports car, is it a jeep (or something with towing power), is it a minivan or SUV, is it 4 door? 2 door? then you expand on it "what is the best car for driving on water?". You need a boat.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

DesNorton SSC-Insane Points: 24399 More actions · Answer 3

You could also create a schema per city, and create views that filter the data accordingly. Then grant the users access to the appropriate views.

How to post data/code on a forum to get the best help.

BoobyB SSCertifiable Points: 5470 More actions · Answer 4

Hi All,

Thanks for the suggestions; they all are valuable and so much appreciate it.

B