What is the best way allow third-party access to a limited view of your database?

Question

What is the best way allow third-party access to a limited view of your database?

Ozymandias_EBON

SSC-Addicted

Points: 440
More actions
January 31, 2019 at 9:52 am

#381336

Hello Very Smart People!
My company has a partner that does a lot of data analysis for us.
In the past, they reached out to a particular person for data extracts and would wait weeks before getting a reply.
I would like to streamline the process by giving them restricted access to our Azure SQL server.
My thought was to create a schema (let's call it READ-ONLY-SCHEMA), assign a user to the schema (let's call the user READ-ONLY-USER), and publish views to the schema (let's call the view READ-ONLY-SCHEMA.LimitedDataView).
This would allow them to get real-time data from the views without having to wait for someone to extract a file and transmit it.
So I start down my merry way and I get stopped by CDOC.
"The SELECT permission was denied on the object..."
The issue is that the view references tables in another schema (PRIVATE-DATA-SCHEMA.ReallyValuableTableData).
Searching for solutions it seems the most common it give the user SELECT access to PRIVATE-DATA-SCHEMA.
When I do this, not only can they see the view but they can see all of the tables in PRIVATE-DATA-SCHEMA
So my questions are:
Is there a way to expose READ-ONLY-SCHEMA.LimitedDataView, referencing tables from PRIVATE-DATA-SCHEMA, without exposing the tables from PRIVATE-DATA-SCHEMA?
Is there a better way to do what I need?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the issues is object owner chaining. cross schema or cross database queries break the object chaining inheritance, and require select permissions to teh underlying tables.
you don't want that, you want to stick with where the user does not need access to the underlying tables.

the fix is simple, don't use a separate schema..if all the objects are dbo.TableXXX, create a view dbo.view....
to keep things understandable
so CREATE VIEW [dbo].[PrivateDataPrefix_viewName] AS ....will solve the issue.

then grant them SELECT just to the specific views you create...
GRANT SELECT ON [dbo].[PrivateDataPrefix_viewName] TO MyVendor

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!