The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.



asked 13 Jan '12, 08:16

TimC's gravatar image

accept rate: 0%

edited 13 Jan '12, 09:54

Volker%20Barth's gravatar image

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.

(13 Jan '12, 09:09) Breck Carter

I belive they primarily mean backup & recovery.

(13 Jan '12, 10:44) TimC
Replies hidden

That you can give them without DBA authority:

(13 Jan '12, 12:35) Breck Carter

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

permanent link

answered 13 Jan '12, 08:33

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

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

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
showing 1 of 9 show all flat view

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

permanent link

answered 13 Jan '12, 11:26

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

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'**&&*&$)...'
permanent link

answered 16 Jan '12, 16:34

Bill%20Aumen's gravatar image

Bill Aumen
accept rate: 16%

...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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 13 Jan '12, 08:16

question was seen: 1,669 times

last updated: 17 Jan '12, 03:36