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.
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.
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
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
>> 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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
re:
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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....
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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.