Protecting intellectual property

[Former Member]

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

[Breck_Carter]

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

[Former Member]

I belive they primarily mean backup & recovery.

[Breck_Carter]

That you can give them without DBA authority:

GRANT CONNECT TO BACKER IDENTIFIED BY "SQL";
GRANT BACKUP TO BACKER;

[Former Member · Accepted solution]

See the help for the ALTER PROCEDURE .... SET HIDDEN syntax.

[Former Member · Accepted solution]

Is this one-way? I.e. can a dba run something like SET UNHIDDEN?

[Breck_Carter · Accepted solution]

It is one-way.

[Former Member · Accepted solution]

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 😉

[VolkerBarth · Accepted solution]

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

[VolkerBarth · Accepted solution]

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.

[Breck_Carter · Accepted solution]

...which means you delete and restart the log before delivering the database.

[VolkerBarth · Accepted solution]

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

[Breck_Carter · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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

[Former Member]

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

[VolkerBarth]

...yes, as stated (somewhat hidden) in one of my comments on Glenn's answer and in this cited FAQ...

A truly "hidden" feature:)

[VolkerBarth]

In case the customer-site DBA primarily need to do maintenance tasks like backup/validation, you can

• grant them BACKUP and VALIDATE authority
• or encapsulate such facilities within events (that do these task automatically)
• or encapsulate such facilites within stored procedures and grant them execute permissions.

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).