data security for a data warehouse

  • I'm working with someone whose client says "Yes, we have a data warehouse. But NO you can't be granted access to it."

    Technically, can you not create one or more schemas and add views to the schema ( and the other necessary required objects ) so that the outsiders are granted some but not full access and can do their jobs? Yeah, create a security group, add all the contractor users to the group, and go that route... (Or is that just an insane security hole?)

    I'm just not in the mood to create a data warehouse from the bits and pieces we've been granted access to. A lot of it doesn't even make much sense, because it's missing the relationships to other tables.

    Any suggestions on how to handle a mess like this?  Anything I should read?



  • There might be various good reasons why access to a production data warehouse cannot be granted: It may be a heavily regulated environment, with PII or PHI. Or they might have a development environment that closely mirrors production, in which case you wouldn't need direct access to the production environment.

    That being said, I am always perplexed why companies would not at least give you read access if it will help your development or troubleshooting efforts.

    For me personally, it comes down to what I need in order to be successful (get the job done) and what I need in order to maximize efficiency and be most effective. I communicate both explicitly and let the customer decide how they want to proceed. If they want to pay for the extra time it'll take to jump through hoops then I'll happily jump through those. If however the environment and/or process means I am destined to fail, I politely decline to get involved.

  • I'm a very firm believer in the least privilege principal. You should be barely have enough access to get the job done. However, you should have enough access to get the job done.

    Yes, there are a number of mechanisms that they could use to give you limited access to the existing data. Just sounds like they don't want to do the work. So, yeah, you're going to have to sell them on the concept. I'd say it's down to measuring & documenting the degree of wasted time you have because you're being forced to scrape other resources for data they could readily provide you.

    One resource that might help is this book by Denny Cherry. It's a bit older at this point, but the information, especially the conceptual stuff, is still excellent.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Come to think of it, if they paid for like 3 months of an Azure subscription, they could dump whatever they wanted there, and we could use pipelines in Data Factory to reassemble something like their data warehouse, and then when the plug gets pulled, all that disappears. (For some reason, a bunch of it is in BLOB storage now... would be much easier to just reassemble into a database there and do that, I think.

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

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