Avatar of DaveKid
DaveKid
 asked on

How to Give Data to a Client without Giving them your Intellectual Property

We have a client that "owns" the data on our server.   They are asking us for said data but we don't want to just give them a database backup as that we essentially be handing them a huge chunk of building the system themselves (which they are looking to do)

Any advice on how to give them a "data pull" without handing them the actual table structures and without it taking a ton of manual effort to "obfuscate" it?

Obviously, I know how to leave out the SP's and Functions but SP's can easily be generated automatically via scripts.
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
DaveKid

8/22/2022 - Mon
lcohan

You could create a SQL login with "datareader" only access to that database and they should be able to connect to the database using that SQL login and only read the data or.....
Backup the original database, restore it under different name on the same server for instance, then run a cleanup script to remove ALL SQL code(SPs, UDFs, Triggers, etc., FKey's and anything else you want to "hide") then backup that database and pass it to your client. This way they will get only tables with data.
DaveKid

ASKER
one more question (cause my boss changed his mind again).  

is there an easy way to give just the tables BUT obfuscate the column names by writing a script that goes in and updates all the column names to something like "column1, column2, column3" (assuming I first drop all the pks and fks)

again, I know that just knowing a schema of tables doesn't give someone that much but we suspect the company we are handing this to is going to try to use it to recreate the application themselves and unfortunately we never had agreements in place that says they can't take our schema and do just that.  We just want to make it as difficult as possible
Scott Pletcher

>> is there an easy way to ... <<

Yes:
Create views in a dfferent schema (to prevent permission chaining from allowing them to see other things automatically) on the existing tables with whatever column names you want, and only give them access to the views.

You don't want to provide "db_datareader" role access to them; that's far too much.

Simply explicitly GRANT SELECT to the views you create.
Your help has saved me hundreds of hours of internet surfing.
fblack61
DaveKid

ASKER
I probably forgot to mention that we are giving them a backup file of the DB so I don't think the view idea would work.  That's why I was suggesting a script that would go through and obfuscate the column names before handing them the backup.

Essentially I would make a backup of the DB, get rid of all the SP's, UDF's, etc, etc, drop all the FK's and PK's, then run the script that automagically updates the column names, then make a backup of that DB and then hand it over.
SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ProjectChampion

The least favourable solution from my point of view is creating a login and giving access to the client to query your database directly - even using VIEWs.

It sounds like what your client needs is just one or more data extracts, so you should be able to generate data extract in the form of a flattened/denormalized CSV or Excel file to include all the data that they need without revealing any of your database schema or granting unnecessary permissions and and poking security holes on your platform.
You may need to ask a few questions from the end user to make sure you have a clear idea of what they need, why they need it and how they are going to use the data, so that you can come up with the best arrangement for the format and contents of the data extract file(s).

If it's not a one-off request and they may need to receive updated data on a regular basis, then you can create SSIS packages to generate and send the data extracts on a regular basis (based on the required frequency) using scheduled jobs.
DaveKid

ASKER
re:
select
    ColA as col1
    , ColB as col2
into dbo.Newtable
from ViewDefHere

While I appreciate the idea, seems pretty labor intensive to me.  Although I suppose I could try and write a dynamic query from system tables.  Anyone know which system tables?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DaveKid

ASKER
Good points...

And yes, in the end we are just going to give them the data portion of the data, remove the stored procedures, etc. as most of the business logic is either there or in the application layer and call it a day....
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DaveKid

ASKER
I am going to split up the points for this evenly amongst everyone, as I feel that everyone's input was important.  (I also just feel that it is most fair that way)