OracleBlog

If you're still using any of the default Oracle username/passwords on any of your databases, go change it now.

I could blog about this for 1-2 pages, but for two reasons:
1. I think it is an obvious (but oft-neglected) thing to do, and
2. You can use the time you would have spent reading my persuasive arguments to actually go change your passwords.

So go do it now.

What is a number? The definition is one of the longest I've seen, but generally it refers to a quantity (or sum, total, count) of units. Quantities can be subjected to all sorts of calculations, such as addition, subtraction, multiplication and division.

Sometimes numbers are used for ordering, as well. So it makes sense to compare one number to another, not only as being greater or lesser than another, but also by what number of units it is greater.

Which brings me to my point. I've been asked on more than one occasion why I sometimes favour the use of VARCHAR2 in situations where others are using NUMBER. Take, for example, my earlier post on creating an appropriate Boolean type.

Some developers think I should have used NUMBER(1) as the type, denote True as 1, False as 0, and have a check to make sure the value is either 0 or 1.

That solution also works. But there is a reason I don't use it.

It is true that this method has its advantages, not the least of which is comfort to programmers who are used to that (most programming languages make that conversion implicitly). They also claim that you can use multiplication for AND operations, and addition for OR operations.

Or can you? True * False = False, so ok, multiplication seems to work fine. But addition? True + True = 2. 2 is not a valid value. And what about subtraction and division? "Oh those don't apply" Then it's not an appropriate use of NUMBER, in my mind. What about square root? What is True to the exponent False? And so on.

I don't mean to pick on this one example. I've seen cases where NUMBER is used for all sorts of things: dates, credit card numbers, social insurance/security numbers, street address house numbers, zip codes, and so on. In some of these cases (though not necessarily all), it would have been more appropriate to create a type: a VARCHAR2 restricted to numerical characters, and then use that one type for all such instances.

No, this is not a critical point. NUMBER will work just fine, and I have no stories about how people got into trouble by using NUMBER for something that wasn't. But consider the importance of having precision and accuracy in your data. Shouldn't we be equally precise in your description of the data?

That is why I generally prefer using NUMBER only in cases where the field actually is by definition a number, a quantity of units, and eligible for all numerical operations. I'd love to hear your thoughts on this matter too.

There are two broad categories of reasons why PL/SQL programmers wouldn't want others to read their code:

Reason #1 Protection
The programmers wish to protect their code from theft, misuse or alteration (among other things).

Reason #2 Malicious Reasons
The code is meant to do harm, or may have some potentially dangerous errors they wish to cover up.

I don't believe in hidden code, because open code is easier to debug, reuse, and it is obviously easier to detect (deliberately or accidentally) harmful code.

But regardless of whether code you have received from a vendor or contractor is protected or not, you should be testing it thoroughly before using it anyway. Plus, these methods are already thoroughly documented in a number of places, not the least of which by expert Steven Feuerstein and also in Oracle's documentation.

In fact, the example I'm using is from Oracle's documentation.

Normally, after creating a stored procedure, the source code can be access through a SOURCE table, like so:

SQL> SELECT text FROM USER_SOURCE
SQL> WHERE name = 'WRAPTEST' order by line;

TEXT
-----------------------------------------------------------

PROCEDURE wraptest IS
TYPE emp_tab IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
all_emps emp_tab;
BEGIN
SELECT * BULK COLLECT INTO all_emps FROM emp;
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Emp Id: ' || all_emps(i).empno);
END LOOP;
END;

How can we avoid this?

My first thought is simply not to load the procedure into the database at all. Leave the procedure as an SQL file on the server, and execute it remotely. About a year ago, I engaged some Oracle programmers in such an approach, check that earlier article for a brief discussion on the advantages and disadvantages of keeping your code out of the database:
PL/SQL Code Storage: Files vs In-DB Packages

Anyone with access to the server will can still read your code, using an editor of some kind. But perhaps your server is capable of restricting access to your satisfaction.

You'll note that those Oracle programmers largely agreed that if your code is in the database, it should be wrapped and/or part of a package. First let's look at wrapping.

Wrapping, otherwise known as obfuscation, is a method of changing your human-readable PL/SQL code into text that is readable only by Oracle. In that sense, it can be compiled and executed like any other code, but it is protected in the sense that it won't make sense to anyone except Oracle.

You can learn how to use the wrap utility from either Dan Morgan, or Oracle's PL/SQL User's Guide and Reference (Appendix C), but I'll show you what I mean right now.

1. Put your PL/SQL code in an SQL file.
2. From the command line, issue the wrap command. The syntax is wrap iname=input_file [oname=output_file]

wrap iname=wraptest.sql oname=wraptest.plb

3. From SQL*Plus, execute the resulting PLB file to load the stored procedure, and even execute it for verification (omitted here).

SQL> @wraptest.plb

Procedure created.

4. Try to read the code, either by opening the PLB file, or querying a SOURCE table. PLB is a standard Oracle extension, sometimes referred to as "PL/SQL Binary." But it isn't binary, it's text: you can go ahead and read it.

SQL> SELECT text FROM USER_SOURCE WHERE name = 'WRAPTEST' order by line;

(garbage removed)

Now is wrapping a perfect solution? Well no. First of all, Oracle doesn't claim that it is undecipherable. Edited: In fact, leading Oracle security expert Pete Finnigan has made a presentation available that summarizes how to read wrapped code.

It also has a few annoying limitations, none of which I'll prove here, but they include the fact that you can't wrap triggers (just call a wrapped proc from your trigger), can't be copied to an earlier release, and all Oracle comments (--) are deleted.

I hear there may be a superior solution in Oracle 10, as part of the DBMS_DDL package. That solution doesn't even require a command-line step, it can be done directly in the database. If you're ahead of me, and using Oracle 10, give it a try using Dan Morgan's reference for the syntax.

Speaking of packages, the more important lesson to learn from those Oracle programmers was to put your code into packages. You can make the specification public, so everyone can understand them, and then restrict access (and wrap) the implementation details.

Using packages and wrapping your code is the standard method of protection. As a closing note, there are plenty of other compelling reasons to use Oracle packages too. I wrote about that once before, and also included some links where you can get the finer details on how to use them:
Oracle Packages - And why/how you should use them.