We help IT Professionals succeed at work.

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

219 Views
Last Modified: 2014-05-12
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.
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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 PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> 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.

Author

Commented:
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.
David ToddSenior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.

Author

Commented:
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?
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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....
David ToddSenior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.