any way to obsfucate a stored proc?

[John Reynolds]

Hi all,
I have a software product that includes stored procedures using sql. Is
there a way to obsfucate or otherwise NOT deliver readable source to a
stored procedure? I don't really want to write the SP in java or C. SQL does
what I need just fine. I want to prevent the customer from viewing the
source of the stored procedure. Also, if I did write the stored procedure in
C is performance generally slower or faster than an equivalent SP in sql.
The SP performs several selects using dynamic sql .
Thanks in advance..

[mairhtin o'feannag]

An SQL stored procedure is still compiled (at present) via a C compiler,
and the code stored within the system. The source is stored (I believe)
in the catalog tables, so the customer would have to be rather savvy to
figure it out, but then, HEY! there are savvy customers!

You're probably better off using a C SP and not delivering the code. It
may prove to be faster, may not, since, as I said, the SQL stored
procedure is compiled into executables anyway.

When delivering the product, you could deliver the executables and update
the catalog to not have the source of the SQL SP stored. <shrug> At
least I *THINK* you might be able to do this.

Mairhtin
"John Reynolds" <no****@nowhere.com> wrote in
news:Hu*****************@fe2.columbus.rr.com:

Hi all,
I have a software product that includes stored procedures using sql.
Is there a way to obsfucate or otherwise NOT deliver readable source
to a stored procedure? I don't really want to write the SP in java or
C. SQL does what I need just fine. I want to prevent the customer from
viewing the source of the stored procedure. Also, if I did write the
stored procedure in C is performance generally slower or faster than
an equivalent SP in sql. The SP performs several selects using
dynamic sql . Thanks in advance..

[Serge Rielau]

Take a look at the GET_ROUTINE and PUT_ROUTINE procedures.
You can deploy a stored procedure without it's source code.
That's one of teh main reasons why GET and PUT still makes sense in DB2
V8.2.

Cheers
Serge

[Norbert Munkel]

Hello,

mairhtin o'feannag wrote:

An SQL stored procedure is still compiled (at present) via a C compiler,

This is true for versions prior to 8.2.. There is no c-compiler needed
for writing sql stored procedures on Stinger.

Norbert

[John Reynolds]

Thanks, that works great to hide my source of the SP. I can not find a
similar command or syntax that allows the same for a trigger. Can a trigger
be delivered in a similar fashion?

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2t*************@uni-berlin.de...

Take a look at the GET_ROUTINE and PUT_ROUTINE procedures.
You can deploy a stored procedure without it's source code.
That's one of teh main reasons why GET and PUT still makes sense in DB2
V8.2.

Cheers
Serge

[Serge Rielau]

Hi John,

Not as such. If you are on DB2 V8.2 you may be able to push teh logic
into a procedure and use a CALL.
This works for Triggers and SQL UDF alike.
The problem with these objects is that they are macro expanded into the
invoking statement (like a view) so DB2 needs the code.

May I ask a curious question though for my own education?
Do you just want to hide the text from _plain_ sight or do you actually
want to encrypt.
The reason why I ask is that all the information about a trigger,
function, check constraint... is also stored in teh packed descriptor of
the table/function (which is a BLOB). So simply removing it from the
TEXT column is conceptually easy. But a simple db2cat maintenace command
would unravel the mysteries ;-)

If you wanted encryption. Would YOU need to be able to descrypt (using a
password)?

Cheers
Serge

[JS]

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...

Take a look at the GET_ROUTINE and PUT_ROUTINE procedures.
You can deploy a stored procedure without it's source code.
That's one of teh main reasons why GET and PUT still makes sense in DB2
V8.2.

Cheers
Serge

my experience on windows platform is that get and put may not work
correctly, if you get sql0444n reason code 4 when calling the SP the
fun is just beginning!

[John Reynolds]

Yes , I am on 8.2 on a Windows XP platform, I tested the get/put, and had no
issues like JS describes. I have pushed most logic down into the SP. The
trigger doesn't contain anything really novel. My product is protected by a
signed software license agreement with the customer (not shrinked wrapped or
EULA), so hopefully the customer would honor that. However, I am the
paranoid type, and would like to still make it as difficult as possible for
a 'well meaning' programmer to copy/paste the code to an in-house
development effort, ... or (in this case) another vendor dealing with other
apps that access the database 'inadvertently' view the code and become aware
of the techniques used. So to answer your question, I am looking for a truly
secure delivery method that allows the SP to be executed, but cannot be (not
without great difficulty) viewed or reversed engineered, etc. Encryption is
the best answer, depending on when/who does the decryption at run time and
if other processes could spy on the process to read the unencrypted
executable byte codes. Also, SQL traces would reveal hints as to the inner
workings of the SP, so it would be nice if SQL issued by an encrypted SP
could not be traced (at least from db/2). It doesn't protect against network
sniffers, other hacks, etc. but for my purposes, if it is difficult enough
that you have to go out of your way to view the source, or your novice or
decent programmer can't figure out how to view it, ... that's good enough.
John

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:2t*************@uni-berlin.de...

Hi John,

Not as such. If you are on DB2 V8.2 you may be able to push teh logic into
a procedure and use a CALL.
This works for Triggers and SQL UDF alike.
The problem with these objects is that they are macro expanded into the
invoking statement (like a view) so DB2 needs the code.

May I ask a curious question though for my own education?
Do you just want to hide the text from _plain_ sight or do you actually
want to encrypt.
The reason why I ask is that all the information about a trigger,
function, check constraint... is also stored in teh packed descriptor of
the table/function (which is a BLOB). So simply removing it from the TEXT
column is conceptually easy. But a simple db2cat maintenace command would
unravel the mysteries ;-)

If you wanted encryption. Would YOU need to be able to descrypt (using a
password)?

Cheers
Serge

[Serge Rielau]

You kind of confirm my thinking that there are three levels of paranoia:
1. Thou shall not invite theft of IP
(I.e. if you're not malicious you will not get tempted)
2. Encryption of the static code
3. Encryption of tracing.

Points two and three are more tricky.
Thing is if something goes wrong:
* you want to get access
* DB2 supports wants to get access
This really means two copies.
One encrypted with your password, one with IBMs
Also in case of a trigger, function or even a view teh DB2 engine must
be able to unravel the mystery.

The third problem is the toughest.
If you want to drive the point home then even an optimizer plan is
living in teh danger zone, along with the SQL Procedure Tracer I
introduced in V8.2. Even db2trc is dangerous.

Cheers
Serge