Oracle's PL/SQL
PL/SQL is the language used for creating stored procedures, functions, triggers, and objects in Oracle. It stands for Procedural Language/SQL and is based on the ADA programming language. PL/SQL is so integral to Oracle I'd recommend getting a book on it and reading it, but in the meantime here's a quick one-minute lesson. Here's the code for the ubiquitous “Hello, world!”:
If you run this procedure with
EXEC HELLO_WORLD
and you don't get any output, run
SET SERVEROUTPUT ON
Essentially, this procedure calls the PUT_LINE procedure defined in the DBMS_OUTPUT package. A PL/SQL package is a collection of procedures and functions (usually) related to the same thing. For example, we might create a bunch of procedures and functions for modifying HR data in a database that allows us to add or drop employees, bump up wages, and so on. We could have a procedure ADD_EMPLOYEE, DROP_EMPLOYEE, and BUMP_UP_WAGE. Rather than have these procedures just free-floating, we could create a package that exports these procedures and call the package HR. When executing the ADD_EMPLOYEE procedure we'd do
EXEC HR.ADD_EMPLOYEE('David'),
If this package was defined by SCOTT and PUBLIC had execute permissions to execute the HR package, they could do so by calling
EXEC SCOTT.HR.ADD_EMPLOYEE('Sophie'),
So, what's the difference between a PL/SQL procedure and a function? Well, a function returns a value whereas a procedure does not. Here's how to create a simple function:
CREATE OR REPLACE FUNCTION GET_DATE RETURN VARCHAR2 IS BEGIN RETURN SYSDATE; END;
This function simply returns SYSDATE and can be executed with the following:
SELECT GET_DATE FROM DUAL;
Needless to say, PL/SQL can be used to create procedures that contain SQL queries and further, if PL/SQL can't do something, it's possible to extend PL/SQL with external procedures — more on this later.
Okay, lesson over; let's get down to PL/SQL and security. When a PL/SQL procedure executes it does so with the permissions of the user that defined the procedure. What this means is that if SYS creates a procedure and SCOTT executes it, the procedure executes with SYS privileges. This is known as executing with definer rights. It is possible to change this behavior. If you want the procedure to execute with the permissions of the user that's running the procedure, you can do this by creating the procedure and using the AUTHID CURRENT_USER keyword. For example:
CREATE OR REPLACE PROCEDURE HELLO_WORLD AUTHID CURRENT_USER AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!'),
END;
When this executes it will do so with the permissions of the user and not definer. This is known as executing with invoker rights. The former is useful for situations where you want some of your users to be able to INSERT into a table but you don't actually want to give them direct access to the table itself. You can achieve this by creating a procedure that they can execute that'll insert data into the table and use definer rights. Of course, if the procedure is vulnerable to PL/SQL injection, then this can lead to low-privileged users gaining elevated privileges — they'll be able to inject SQL that executes with your privileges. We'll discuss this in depth shortly in the section “PL/SQL Injection.”
Another important aspect of PL/SQL is that it's possible to encrypt any procedures or functions you create. This is supposed to stop people from examining what the procedure actually does. In Oracle lingo this encrypting is known as wrapping. First, you have to remember that it's encryption — it can be decrypted and the clear text can be retrieved. Indeed, set a breakpoint in a debugging session at the right address and you can get at the text quite easily. Even if you don't do this you can still work out what's going on in a procedure even though it's encrypted. You see there's a table called ARGUMENT$ in the SYS schema that contains a list of what procedures and functions are available in what package and what parameters they take. Here's the description of the table:
SQL> desc sys.argument$ Name Null? Type ----------------------------------------- -------- ------------------ OBJ# NOT NULL NUMBER PROCEDURE$ VARCHAR2(30) OVERLOAD# NOT NULL NUMBER PROCEDURE# NUMBER POSITION# NOT NULL NUMBER SEQUENCE# NOT NULL NUMBER LEVEL# NOT NULL NUMBER ARGUMENT VARCHAR2(30)
TYPE# NOT NULL NUMBER CHARSETID NUMBER CHARSETFORM NUMBER DEFAULT# NUMBER IN_OUT NUMBER PROPERTIES NUMBER LENGTH NUMBER PRECISION# NUMBER SCALE NUMBER RADIX NUMBER DEFLENGTH NUMBER DEFAULT$ LONG TYPE_OWNER VARCHAR2(30) TYPE_NAME VARCHAR2(30) TYPE_SUBNAME VARCHAR2(30) TYPE_LINKNAME VARCHAR2(128) PLS_TYPE VARCHAR2(30)
There's a package called DBMS_DESCRIBE that can also be used to “look into” such things. The text of DBMS_DESCRIBE is wrapped, so let's use this as an example of how to use the ARGUMENT$ table to research a package.
First you need the object ID of the DBMS_DESCRIBE package — this is from Oracle 9.2, incidentally:
SQL> select object_id,object_type from all_objects where object_name = 'DBMS_DESCRIBE';
OBJECT_ID OBJECT_TYPE
---------- ------------------
3354 PACKAGE
3444 PACKAGE BODY
3355 SYNONYM
You can see the object ID is 3354.
Now you take this and list the procedures and functions on DBMS_DESCRIBE:
SQL> select distinct procedure$ from sys.argument$ where obj#=3354 PROCEDURE$ -------------------------- DESCRIBE_PROCEDURE
Turns out there's only one procedure in the package and it's called DESCRIBE_PROCEDURE. (Note that while the package specification may only contain one procedure the package body, that is, the code behind the package, may have many private procedures and functions. Only the public procedures and functions can be called.)
To get the list of arguments for the DESCRIBE_PROCEDURE procedure you execute
SQL> select distinct position#,argument,pls_type from sys.argument$ where obj#=3354
and procedure$='DESCRIBE_PROCEDURE';
POSITION# ARGUMENT PLS_TYPE
---------- ------------------------------ ------------------------------
1 OBJECT_NAME VARCHAR2
1 NUMBER
1 VARCHAR2
2 RESERVED1 VARCHAR2
3 RESERVED2 VARCHAR2
4 OVERLOAD
5 POSITION
6 LEVEL
7 ARGUMENT_NAME
8 DATATYPE
9 DEFAULT_VALUE
10 IN_OUT
11 LENGTH
12 PRECISION
13 SCALE
14 RADIX
15 SPARE
If the PLS_TYPE is not listed it's not your standard PL/SQL data type. In this case arguments 4 to 15 are of type NUMBER_TABLE.
You can see how quickly you can begin to derive useful information about wrapped packages even though the source isn't available.
Incidentally there's a buffer overflow in the wrapping process on the server that both Oracle 9i and 10g are vulnerable to. A patch is now available but the buffer overflow can be triggered by creating a wrapped procedure with an overly long constant in it. This can be exploited to gain full control of the server.
So before we continue, here are the key points to remember. First, by default, procedures execute with definer rights — that is, they execute with the privileges of the user that defined or created the procedure. While this can be useful for applications, it does open a security hole if the procedure has been coded poorly and is vulnerable to PL/SQL Injection.