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.

Say, I have a function whose contents should be hidden. The general way to create such a function needs two steps:

:::SQL
create function myFunction() returns varchar(255) 
begin
   return 'SomethingNearlySecret';
end;

alter function myFunction
set hidden;

After the second step, the function's original definition is obfuscated and cannot be queried from the system tables.

However, the current transaction log contains both statements and therefore still has the definition available. The same would be available when the statements will be distributed by SQL Remote's passthrough mode, and the remote would run in verbose mode.

Is there a way to create a hidden function (or procedure, trigger or view) in one step, not leaving the original definition in the log file?

(Note: I'm aware that this is just obfuscation and not strong encryption.)

asked 31 Mar '11, 07:10

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%


A valid solution seems to be the following, even across different SA versions:

  1. Create the function as documented in two steps - but in a different database, not in the original database. Possibly create that database just for this purpose.
  2. Unload the function's definition from that particular database, say by DBUNLOAD -n.
  3. The reload.sql file will contain a create function statement with the undocumented CREATE ... HIDDEN syntax, something like

    create function myOwner.myFunction hidden '(()(()**...$';

  4. This particular statement can be run against the original database and will create the hidden definition without leaving the original statement in the log.

This seems to work with ASA 8.0.3 and SA 12.0.1 though I'm not sure whether the hidden definition is truly version-independent.

permanent link

answered 31 Mar '11, 07:24

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 31 Mar '11, 07:26

Just a remark: The hidden definition does also contain information about the function's parameters and return type. This is still available in system table sysprocparm - for obvious reasons: Otherwise, the function would not be usable anymore IMHO...

(31 Mar '11, 07:32) Volker Barth

To the iAnywhere experts:

Is my assumption that this approach will work over different versions (at least v8 - v12) correct?

(01 Apr '11, 03:33) Volker Barth

Another workaround (or enhancement, depending on your point of view) might be to store the critical core text of the procedure in a LONG VARCHAR column in a CREATE TABLE ... ENCRYPTED, and run it with EXECUTE IMMEDIATE.

Kludgy yes, but encryption beats obfuscation.

Breck

permanent link

answered 01 Apr '11, 08:26

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

1

Do you suggest to store in a table/column with DBA-only access? Otherwise, the table encryption wouldn't protect against querying that table, only against database file inspection.

And in case the column contents itself should be enctrypted, then the according encryption key must be stored anywhere else, lifting the encryption/obfuscation discussion to the next level:)

Besides that, an interesting suggestion, for sure.

(01 Apr '11, 08:37) Volker Barth
Replies hidden

Oh well, I'm too good at obfuscating, should have written "encrypted" instead of "enctrypted". One of my key problems:)

(01 Apr '11, 08:46) Volker Barth
1

I think we can all decipher your meaning.

(01 Apr '11, 09:41) Siger Matt
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×100
×37
×30
×26

question asked: 31 Mar '11, 07:10

question was seen: 1,486 times

last updated: 01 Apr '11, 09:41