Oracle s PLSQL

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!":

 CREATE OR REPLACE PROCEDURE HELLO_WORLD AS BEGIN       DBMS_OUTPUT.PUT_LINE('Hello, World!'); END; 

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 proceduresmore 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 privilegesthey'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 encryptionit 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 packagethis 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 rightsthat 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.