How to make a DB a "black box"
-
G'day all,
I have a need to share a DB with a vendor. The vendor will need to use a library, provided by me, to call stored procedures contained in the DB. It is mandatory that the DB is a complete black box to the vendor as the internal structure of the DB, as well as how the stored procs are implemented, are a major component of intellectual property. The vendor also does work for our competitors.
Is it possible to lock down a DB to the degree I am suggesting?
Where can I find a description of how to do so?
Thanks in advance for any thoughts or ideas, and have a great day!
Wayne
-
Assuming you mean "Given SQL Server"...
I suppose if you completely encapsulated all access via DLLs or other client applications it might be possible. (By way of shipping a finished db and server application and not giving SA access to client). But then you'd have to reinvent the wheel and provide all administration functionality and all installation functionality in your client or installation applications.
Because, fundamentally if they have SA, they can see anything to do with the structure.
You used to be able to delete the source of the stored procedure (in syscomments) without deleting the object code (haven't tried in the latest version). But that does not protect the base tables from view.
Then you can always do what another fellow I knew did: He made a completely non-intuitive design, named columns/tables with generic or non-revealing names... kind of like spaghetti code a la database: it required the developer to fix anything or even make some fairly simple reports for the client.
Perhaps if you kept the database physically within your control, open via the web (behind appropriate firewall filtering port/IP to restricted source IPs, etc.) Even then, the client application (or wrapper client functions) would have to hard code/encrypt the password for access.
...that's as much as I know, surely someone else knows something more or a better way... But what are you worried about anyway? Why not just go the lawyer route with terrifying NDA, etc.?
(BTW: don't take this reply as implying that I, in anyway, support or condone the concept of intellectual property. IP is a terrible evil.)
-
"Given SQL Server" is correct
This is an internal system - not a product. The production environment is under such tight control that I am not even allowed to see the door to the server room.
The vendor is writing a client app that will interface to the backend via a DLL. Eventually, the DLL will be exposed as a service and everything becomes much easier then. The service is several months out. We are trying to figure out how to provide the vendor a copy of a live DB to develop against without giving away the entire business.
As far as why this is an issue? A significant portion of our business comes from our direct competitors. We do their work and they deliver it to their customers under their name. They come to us because of our IP.
I do not believe that a full "black box" is possible as requested, but am hopeful that many of you smarter than I am can point me in the right direction.
Regards,
Wayne
-
So all DB access is via your library? From what I can see, giving them the DB won't help anyway because they need to develop against your library, not your stored procs (unless your library just directly calls the SPs with little parameter translation).
You have two options...
1) Give them the library with no executable code in it - they shouldn't need the DB or a live system if you have documented it thoroughly..
2) Give them the list of stored procs and parameters and good guidelines on how your library will call them...
#1 is probably best and is what most developers are used to - I read the MS documentation on some system calls in MSDN and make use of them - I don't need or want to know how they work
-
Depends on what you are really trying to do....you could always create a VIEW of your database with the VIEW having different column names to hide the actual names.
-SQLBill
-
How large is the database?
What about setting up a custom MSDE instance install, with SA password that only you know, and with only SQL logins that you control (in your DLL). Disable the Builtin Administrator and Guest roles and no one will be able to get into the database besides your own access.
If your database is < 2gb, and since this is for their testing only, and assuming you use pooled ADO connections from your DLL, MSDE should be a very good fit for this situation.
Hope this helps
Mark -
Ian and Mark,
Thank you!
Ian: This is exactly where we are today. The vendor is requesting that we provide a DB so they can see end-to-end functionality before delivering the code to us.
Mark: You are describing the scenario I hoped existed. The DB will be a tiny one, with just enough data for dev and initial QA. Once the development is finished then we will bring the app in house for full qa and acceptance. I will start down your suggested path and see how far we get. The biggest battle islikely to be the political one rather than the technical.
Thank you all for the suggestions, and have a great day
Wayne
-
If the value of the database structure and procedures is very high, then you must not yield physical control of the database instance, i.e. files, to the vendor. Only allow a remote connection (VPN if necessary) to a server that you control. It is too easy to view the information through other means that don't enforce Windows or SQL Server security controls.
In any case, technical measures are not enough. I hope you have very strong language in your contracts with the vendor to prohibit reverse-engineering or any other use of your intellectual property besides creating this application.
-
Granted there are ways to restrict things, however this realy seems like a thing for the contract people (lawyers) at your firm. A simple acronym - NDA (non-disclosure agreement). If your company or the vendor do not have things like this in place then I'd seriously start thinking about a change of scenery ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
A possible problem with Mark's MSDE solution:
- They own the server MSDE is running on
- They stop the service
- They copy the database files to another server
- The attach it there, where they have SysAdmin rights
I like the later VPN idea myself, but it sounds like your corporate climate/lockdown might prevent that from happening any time soon.
Philip
-
G'day All,
Thank you for all of the discussion and suggestions.
The approach we have decided to take is to speed up development and deployment of the libary as a service. We can deploy the service to a server in the DMZ while maintaining the DB in the back room. This should allow our devlopment houses around the world to access our backend systems while supporting the company needs to maintain physical control of the DBs.
Politically, we can open up a VPN to a service much easier than we can open up a direct link to the back end.
The discussion from all of you really helped confirm that the risk is significant, and helped us explain the need to speed up development of the right solution.
Have a great day!
Wayne
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply