|
We're considering developing a new application for re-sale. Enquries with potential clients is positive but some have said that thier DBAs will need to administer the db. How do I protect the IPR contained within the stored procedures? I don't mind them accessing the data but I wan't to protect the code. Cheers, Tim |
|
See the help for the ALTER PROCEDURE .... SET HIDDEN syntax. Is this one-way? I.e. can a dba run something like SET UNHIDDEN?
(13 Jan '12, 08:46)
TimC
Replies hidden
It is one-way.
(13 Jan '12, 09:10)
Breck Carter
Hi, this is very nice question, I just gave it a try with SET HIDDEN, and really, not even powerdesigner can check the content of the procedure. Really nice feature, didnt have a clue about it before ;)
(13 Jan '12, 09:23)
marchello
... but a DBA could easily call DROP PROCEDURE or ALTER PROCEDURE BEGIN -- myCode replaces yourCode -- END... (Do I sound paranoid? - Well, you asked a security question...) So giving customers DBA authority will usually mean they generally are able to modify the database in ways that don't work with your app anymore. Therefore, if you just want them to be able to do certain maintenance tasks like backup/validation, user managemenent, encapsulatung these facilities into stored procedures (with execute permissions for the customers) or into events might be better. And in case they need to be able to add their own tables (but not to modify yours), RESOURCE authority would be an option.
(13 Jan '12, 09:52)
Volker Barth
1
Note, that the original definition may be contained in the transaction log and can be translated with DBLOG, cf. this FAQ that deals with exactly that topic.
(13 Jan '12, 09:56)
Volker Barth
...which means you delete and restart the log before delivering the database.
(13 Jan '12, 12:37)
Breck Carter
...yep, and for update scripts (i.e. updating an already delivered database) you can use the "create as hidden" approach from the cited FAQ, if required.
(13 Jan '12, 12:47)
Volker Barth
I can't think of any way to deliver a script to CREATE or ALTER stored procedures in the customer's hands without revealing their contents... the CREATE or ALTER script itself must be in the clear, with the SET HIDDEN coming later to close the barn door after the cows have escaped. Also, a high-privilege connection is required to run the script, thus revealing a high-privilege user id and password. One solution is to only allow the vendor to run the script, another (which Foxhound uses) is to deliver a new *.db file and migrate the data from the old database... there are several other advantages to the latter approach.
(13 Jan '12, 17:13)
Breck Carter
As to the SET HIDDEN clause, you can do CREATE procedure with already hidden contents - that what I've pointed to in the FAQ. Nevertheless, the point about the connection with DBA (or at least RESOURCE) authority may still hold true - unless you have some special procedure (or particular app) that can be used by not so priviledged users to "read and apply a particular schema update file. In the cited case, that particular connection was SQL Remote applying update scripts in passthough mode, with the Remote DBA authority ... so just a builtin security feature:)
(14 Jan '12, 08:16)
Volker Barth
|
|
In case the customer-site DBA primarily need to do maintenance tasks like backup/validation, you can
All these tasks do not need DBA authority. In order to protect the source code of your database objects, use SET HIDDEN for all your views, procedures/functions, events and triggers, as Glenn has told. Additionally, you may be able to restrict the access to system tables (i.e. "REVOKE SELECT on sysXyz from MyUsers" - though that is limited to some degree, AFAIK). |
|
Another way to distribute procedures/functions: if you do an "unload" of the schema, the hidden proc is extracted in enrypted form, ready for distribution without being visible. Here is the unload file for one of our hidden procs: create procedure app_owner.GRANT_DB_ACCESS hidden'**&&*&$)...' go ...yes, as stated (somewhat hidden) in one of my comments on Glenn's answer and in this cited FAQ... A truly "hidden" feature:)
(17 Jan '12, 03:36)
Volker Barth
|
What exactly do they mean by "administer the db"? That could mean taking responsibility for the backup and recovery, stuff like that. It could also mean altering the schema... if you give DBA authority to your customers then you are giving up a lot of protection. This is a topic of much interest to me since 99% of the Foxhound IP is embedded in stored procedures... customers have complete query access to "their data" but never DBA authority. http://www.risingroad.com/foxhound/faq/FAQ-How-do-I-run-adhoc-queries-on-the-Foxhound-database.html
I belive they primarily mean backup & recovery.
That you can give them without DBA authority: