Home » Developer & Programmer » Precompilers, OCI & OCCI » Protecting your SQL queries (Oracle 11g Windows XP)
Protecting your SQL queries [message #513473] Mon, 27 June 2011 14:18 Go to next message
jhmansart
Messages: 6
Registered: June 2011
Junior Member
Hi there
I am not 100% sure this is the right forum for my question but I hope it is.

I am a developper wishing to protect his application from reverse engineering.

My application depends heavily on Oracle and I don't want someone to start tracing the sql I am issuing.

Context : I work for large institutions and have no control on their Oracle environment. I have no administrator right and cannot query v$session to know if my session is being traced.

A solution could have been to put all my sql into stored procedures and then encrypt them but again, I have no administrator rights...

My only hope as of today is that there exist some kind of command like

SQL>select sys_context('SESSION', 'SQL_TRACE') from dual;

which would allow me to query the state of my session from the OCI and then halt my program if the return was true.

But I have been googling for so long now without finding the slightest bit of information that I am afraid there is no such command.

Actually, I have the feeling I am the only person on earth trying to protect his SQL queries while all the other folks have only one goal : succeed to get Oracle trace more and more things from their user sessions.

Is there anyone at Oracle Faq's able to help me ?

Thanks you all for your reading. I hope someone have an idea to help me !
Re: Protecting your SQL queries [message #513475 is a reply to message #513473] Mon, 27 June 2011 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if you could know if you are in SQL trace mode or not this will not prevent a user from activating SQL*Net trace or even sniffing his network.
I'm afaid there is nothing you can do if your client wants to know which SQL you executes and I should it is his right to know what you do on his server. I wouldn't want to purchase a product that does not allow me to audit what it does. Why not then picking up all his data and security policies and network accesses and so on? You could put any virus you want in your code and don't allow the client to know it...

Regards
Michel
Re: Protecting your SQL queries [message #513547 is a reply to message #513475] Tue, 28 June 2011 02:55 Go to previous messageGo to next message
jhmansart
Messages: 6
Registered: June 2011
Junior Member
Thanks Michel for your quick answer.

[The client] his right to know what you do on his server. I wouldn't want to purchase a product that does not allow me to audit what it does

Well, can you audit what Microsoft does on your system ? But I can already guess you don't buy Microsoft, only open source

Why not then picking up all his data and security policies and network accesses and so on?

I have to read his data to run my code on them anyway and of course, I am welcome to do so. Now as to 'pick up the data', the client protects his network the usual way (I cannot access an ftp site, I cannot write to a USB stick,... the usual stuff).

'Pick up the security policies and network accesses' ?? I don't understand your point. I guess all these things are access protected. It is not by tracing session queries that you protect a system !

You could put any virus you want in your code and don't allow the client to know it...

This sounds like science fiction to me. We are talking of Oracle here, not Operating Systems. A virus in an Sql query ? If you are thinking of causing damage to a database by issuing a nasty Sql, I understand your point. But again, tracing the Sql will not prevent this. Though it will give an evidence against the author. So ok, I follow you on this one

As a conclusion, according to you, it is normal that the client protects his environment but it is not normal that the programmer protects his know-how.
Re: Protecting your SQL queries [message #513553 is a reply to message #513547] Tue, 28 June 2011 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Well, can you audit what Microsoft does on your system ?

Yes, I can.
But we are not talking about OS here but external product and, if you want to keep this analogy, my anti-virus checks all actions done by external product on the OS or physical layer and trace (if not block) those that seem doubtful. There are some products that do the same thing for database accesses.

Quote:
It is not by tracing session queries that you protect a system !

Tracing sessions allow to know what products do (or try to do) and you do not PROTECT the system with it but you AUDIT them which may understand who does or tries to do wrong and how you can more protect the system.
And yes, you can get more data on the system you run and other ones with SQL.

Quote:
This sounds like science fiction to me.

Really?
Quote:
We are talking of Oracle here, not Operating Systems

And then don't you know that Oracle can hurt the system?

Quote:
virus in an Sql query ?

Yes.

Quote:
But again, tracing the Sql will not prevent this.

Maybe not the first attack but it can prevent from the next ones.

Quote:
As a conclusion, according to you, it is normal that the client protects his environment but it is not normal that the programmer protects his know-how.

This is not what I meant but the master here is the client nor the programer and if your "secret" is just in a couple of SQL statements then I think it is not worth much.
See all the database products that are on the market, no one thinks about protecting its SQL statements.

Regards
Michel
Re: Protecting your SQL queries [message #513555 is a reply to message #513553] Tue, 28 June 2011 03:45 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
On a related note: I then sincerely hope your software is bug-free and will NEVER cause any errors. Because tracing the SQL is sometimes the only way to find bugs the "developer" is not able to find.

I have a somewhat similar view on wrapped packages. If a developer insist on wrapping his packages, then the application should NEVER throw any unexplained exceptions with reference to a code line number, since I can not look at the code. It should only be allowed to throw meaningful errors like "Product name too long". If a developer is not able to supply such bug-free code, then he should work WITH the customer to fix problems, not work AGAINST the customer by intentionally hiding stuff.
Re: Protecting your SQL queries [message #513709 is a reply to message #513553] Wed, 29 June 2011 02:11 Go to previous messageGo to next message
jhmansart
Messages: 6
Registered: June 2011
Junior Member
Quote:
And then don't you know that Oracle can hurt the system?

No, I did not
Quote:
virus in an Sql query

Didn't know either. You mean some code that can reproduce itself and spread from one machine to another?
It is possible to do that but not prevent Oracle from tracing your session ! Ah ah ! That's a good one !
Quote:
the master here is the client nor the programer

Yes, we all know Bill Gates is not the master. We, the clients, are.
Quote:
if your "secret" is just in a couple of SQL statements then I think it is not worth much

Nice of you Michel. Don't think too much though

Re: Protecting your SQL queries [message #513714 is a reply to message #513555] Wed, 29 June 2011 02:29 Go to previous messageGo to next message
jhmansart
Messages: 6
Registered: June 2011
Junior Member
Quote:
I then sincerely hope your software is bug-free

It is ok so far. On Oracle errors, it creates a logfile so we have the offending query. But my application uses OCI, not stored procedures and so I fight the bugs with the Microsoft Visual Studio debugger

Quote:
If a developer is not able to supply such bug-free code, then he should work WITH the customer to fix problems, not work AGAINST the customer by intentionally hiding stuff.

No one is perfect but after 10 years of hard work for the same company, we never came across the situation you describe.
I do work WITH the customer. That's not the point. The situation is I am working on a database that is provided by another company and it happens that I have a deep knowledge of the Data Model. This allows me to make a living. And I just don't want this technical advantage (that cost me blood and sweat to acquire) to be reverse-engineered and my added value to evaporate.

Re: Protecting your SQL queries [message #513721 is a reply to message #513714] Wed, 29 June 2011 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So I think you will never help someone in forum.
If everyone acted as you do you'd never get help on forum and accordingly you'd have no answer for your question.
So as you posted here you thought that other are keen to share their knowledge but you you won't.
Do you think we acquired what we know without working hard? Wink

Regards
Michel

Re: Protecting your SQL queries [message #513835 is a reply to message #513721] Wed, 29 June 2011 11:35 Go to previous messageGo to next message
jhmansart
Messages: 6
Registered: June 2011
Junior Member
Quote:
So I think you will never help someone in forum.

You think too much. Are you a psychiatrist or something ?

Quote:
If everyone acted as you do you'd never get help on forum and accordingly you'd have no answer for your question.

Ah no, I see you are more like a moralist. But if everyone acted as you, what would the difference be ? People asking technical question and receving admonition ?

Quote:
So as you posted here you thought that other are keen to share their knowledge but you you won't.

Until now, what you are keen to share is hostility. As for the knowledge, you don't seem to have too much to share a part from fairy tales involving viruses in sql...

You are confusing everything my poor Michel.

One thing is to share a knowledge that does not put your business at risk.

Another thing is the business aspect of things. Sharing the design of a highly complex Data Model (a mix of smart and stupid things + historical inheritance) of a System I am an expert of is something else.

I have no problem sharing my MFC, C++ and Java tips and tricks. As you say, these are worthless secrets anyway.



Re: Protecting your SQL queries [message #513837 is a reply to message #513835] Wed, 29 June 2011 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As for the knowledge, you don't seem to have too much to share a part from fairy tales involving viruses in sql...


So what you don't know are just fairy tales... Conclusion: the answer to all the questions you don't know the answer is... no answer exists unless it is a fairy tale. Laughing

Regards
Michel
Re: Protecting your SQL queries [message #513878 is a reply to message #513837] Wed, 29 June 2011 18:13 Go to previous message
jhmansart
Messages: 6
Registered: June 2011
Junior Member
Quote:
So what you don't know are just fairy tales... Conclusion: the answer to all the questions you don't know the answer is... no answer exists unless it is a fairy tale.

If that was the way I think, why would I come here ask serious questions to serious people ?

Previous Topic: Occi Connection Error
Next Topic: Using OCI and distributed transactions with ProC
Goto Forum:
  


Current Time: Thu Mar 28 08:56:04 CDT 2024