Secure Your PL/SQL Code!

View All Scripts Login to Run Script

Script Name Secure Your PL/SQL Code!
Description Developers often think that security is not their problem, their job. Database admins, system admins, network admins: they're the ones responsible for securing applications. Well, sure that's true. But developers also play a fundamental part in ensuring that their company's data is secure. In this script (a co-production of Steven Feuerstein and Rob Lockard, oraclewizard.com), we start with a procedure full of security issues, and step through changes that a developer can and should make to secure their individual program units.
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday April 13, 2016

Statement 1

CREATE TABLE customers  
AS  
   SELECT * FROM sh.customers  
    WHERE cust_id BETWEEN 1 and 1000

Table created.

Statement 2

CREATE TABLE employees 
AS 
   SELECT * FROM hr.employees

Table created.

Statement 3

CREATE TABLE departments 
AS 
   SELECT * FROM hr.departments

Table created.

Statement 4

This is our starting point for a discussion of how to secure one's code. This procedure is a mess.

Super Cool Generic Dynamic Procedure

CREATE OR REPLACE PROCEDURE process_one_row (table_in   IN VARCHAR2,   
                                             where_in   IN VARCHAR2)   
IS   
   l_block   VARCHAR2 (32767)   
      :=    'DECLARE l_row '   
         || table_in   
         || '%ROWTYPE; BEGIN SELECT * INTO l_row FROM '   
         || table_in   
         || ' WHERE '   
         || where_in   
         || '; /* Then do stuff with that row... */'   
         || ' END;';   
BEGIN   
   DBMS_OUTPUT.put_line (l_block);   
   
   EXECUTE IMMEDIATE l_block;   
EXCEPTION   
   WHEN OTHERS   
   THEN   
      Raise_Application_Error (   
         -20000,   
            'Unable to execute dynamic statement for table-where '   
         || table_in   
         || '-'   
         || where_in   
         || ' = '   
         || l_block   
         || ' Error: '   
         || DBMS_UTILITY.FORMAT_ERROR_STACK);   
END;

Procedure created.

Statement 5

And our users always use our code as intended!

Used as Intended

BEGIN  
   process_one_row ('CUSTOMERS', 'cust_id=709; ');  
END;

DECLARE l_row CUSTOMERS%ROWTYPE; BEGIN SELECT * INTO l_row FROM CUSTOMERS WHERE cust_id=709;  END;

Statement 6

That's right. Add an EXECUTE IMMEDIATE statement to the dynamic block. Really? How is this possible? Concatenation is a very indiscriminating operation.

Inject a Table

BEGIN    
   process_one_row (    
      'CUSTOMERS',    
         'cust_id=709;    
      EXECUTE IMMEDIATE ''CREATE TABLE nasty_data (mycol NUMBER)'';');    
END;

DECLARE l_row CUSTOMERS%ROWTYPE; BEGIN SELECT * INTO l_row FROM CUSTOMERS WHERE cust_id=709;    
      EXECUTE IMMEDIATE 'CREATE TABLE nasty_data (mycol NUMBER)'; END;

Statement 7

That's right. Add an EXECUTE IMMEDIATE statement to the dynamic block. Really? How is this possible? Concatenation is a very indiscriminating operation.

Inject a Backdoor Procedure

BEGIN    
   process_one_row ('CUSTOMERS',    
                    'cust_id=709;   
      EXECUTE IMMEDIATE    
         ''CREATE PROCEDURE backdoor (str VARCHAR2)    
          AS BEGIN EXECUTE IMMEDIATE str; END;'';');    
END;

DECLARE l_row CUSTOMERS%ROWTYPE; BEGIN SELECT * INTO l_row FROM CUSTOMERS WHERE cust_id=709;   
      EXECUTE IMMEDIATE    
         'CREATE PROCEDURE backdoor (str VARCHAR2)    
          AS BEGIN EXECUTE IMMEDIATE str; END;'; END;

Statement 8
If you create schema-level procedures and functions, and then grant execute to schemas to run that code, a user connected to that schema can view the source code via a query against ALL_SOURCE. If you move it to a package (with the implementation details, of course, in the body), then they can only see the package specification source.
More secure: Put Subprograms in Packages
```
CREATE OR REPLACE PACKAGE dynamic_hr  
AS  
   PROCEDURE process_one_row (table_in    IN VARCHAR2,  
                              value1_in   IN VARCHAR2,  
                              value2_in   IN DATE);  
END; 
```
```
Package created.
```

Statement 9

The biggest danger in this dynamic PL/SQL procedure is the concatenation of the where clause. It could be and have anything. So we go back to our code base and analyze ACTUAL usages of this very generic routine, and discover that there are really only two patterns. So we convert those patterns from passing a pre-built where clause to instead pass in the argument values that are then bound into the internally created where clause.

More secure: Maximize Use of Bind Variables

CREATE OR REPLACE PACKAGE BODY dynamic_hr    
AS    
   PROCEDURE process_one_row (table_in    IN VARCHAR2,    
                              value1_in   IN VARCHAR2,    
                              value2_in   IN DATE)    
   IS    
      l_where   VARCHAR2 (32767);  
      l_block   VARCHAR2 (32767);     
   BEGIN    
      IF table_in = 'EMPLOYEES'    
      THEN    
         l_where := 'last_name = :name AND hire_date < :hdate';    
      ELSIF table_in = 'DEPARTMENTS'    
      THEN    
         l_where := 'name LIKE :name AND incorporation_date = :hdate';    
      ELSE    
         raise_application_error (    
            -20000,    
            'Invalid table name for process_one_row: ' || table_in);    
      END IF;    
    
      l_block :=   
            'DECLARE l_row '    
         || table_in    
         || '%ROWTYPE; BEGIN SELECT * INTO l_row FROM '    
         || table_in    
         || ' WHERE '    
         || l_where    
         || '; END;';  
  
      EXECUTE IMMEDIATE l_block  
         USING value1_in, value2_in;    
   EXCEPTION    
      WHEN OTHERS    
      THEN    
         Raise_Application_Error (    
            -20000,    
               'Unable to execute dynamic statement for table-where '    
            || table_in    
            || '-'    
            || l_where    
            || ' = '    
            || l_block    
            || ' Error: '    
            || DBMS_UTILITY.FORMAT_ERROR_STACK);    
   END;    
END;

Package Body created.

Statement 10

OK, so no longer allowing a "whatever" where clause to come in. But still concatenating the table name, which could be an injection point. So use DBMS_ASSERT to make sure that table_in value is an actual database object.

More secure: Vaidate the Table Name w/DBMS_ASSERT

CREATE OR REPLACE PACKAGE BODY dynamic_hr   
AS   
   PROCEDURE process_one_row (table_in    IN VARCHAR2,   
                              value1_in   IN VARCHAR2,   
                              value2_in   IN DATE)   
   IS   
      l_where   VARCHAR2 (32767);   
      l_table   VARCHAR2 (32767);   
      l_block   VARCHAR2 (32767);   
   BEGIN   
      l_table := SYS.DBMS_ASSERT.sql_object_name (table_in);   
   
      IF l_table = 'EMPLOYEES'   
      THEN   
         l_where := 'last_name = :name AND hire_date < :hdate';   
      ELSIF l_table = 'DEPARTMENTS'   
      THEN   
         l_where := 'name LIKE :name AND incorporation_date = :hdate';   
      ELSE   
         raise_application_error (   
            -20000,   
            'Invalid table name for process_one_row: ' || table_in);   
      END IF;   
  
      l_block :=   
            'DECLARE l_row '   
         || l_table   
         || '%ROWTYPE; BEGIN SELECT * INTO l_row FROM '   
         || l_table   
         || ' WHERE '   
         || l_where   
         || ';'   
         || 'END;';  
   
      EXECUTE IMMEDIATE l_block  
         USING value1_in, value2_in;   
   EXCEPTION   
      WHEN OTHERS   
      THEN   
         Raise_Application_Error (   
            -20000,   
               'Unable to execute dynamic statement for table-where '   
            || table_in   
            || '-'   
            || l_where   
            || ' = '   
            || l_block   
            || ' Error: '   
            || SQLERRM);   
   END;   
END;

Package Body created.

Statement 11

It would seem at first glance to be appropriate and helpful to provide lots of feedback to the user about what might have gone wrong. Problem is, if your "user" is a malicious hacker, they might be able to use that information to launch a phishing expedition. So log all the details and simply provide a "Contact Help Desk" message.

More secure: Do Not Provide Sensitive Information in Error Messages

CREATE OR REPLACE PACKAGE BODY dynamic_hr   
AS   
   PROCEDURE process_one_row (table_in    IN VARCHAR2,   
                              value1_in   IN VARCHAR2,   
                              value2_in   IN DATE)   
   IS   
      l_where   VARCHAR2 (32767);   
      l_table   VARCHAR2 (32767);   
   BEGIN   
      l_table := SYS.DBMS_ASSERT.sql_object_name (table_in);   
   
      IF l_table = 'EMPLOYEES'   
      THEN   
         l_where := 'last_name = :name AND hire_date < :hdate';   
      ELSIF l_table = 'DEPARTMENTS'   
      THEN   
         l_where := 'name LIKE :name AND incorporation_date = :hdate';   
      ELSE   
         raise_application_error (   
            -20000,   
            'Invalid table name for process_one_row: ' || table_in);   
      END IF;   
   
      EXECUTE IMMEDIATE   
            'DECLARE l_row '   
         || l_table   
         || '%ROWTYPE; BEGIN SELECT * INTO l_row FROM '   
         || l_table   
         || ' WHERE '   
         || l_where   
         || ';'   
         || 'END;'   
         USING value1_in, value2_in;   
   EXCEPTION   
      WHEN OTHERS   
      THEN   
         DECLARE   
            l_id   INTEGER;   
         BEGIN   
            l_id := 1000;  
            /* Use error management package  
               help_desk.pkg_app_errors.f_insert_errors (   
                  p_app         => $$PLSQL_UNIT,   
                  p_line        => $$PLSQL_LINE,   
                  p_errorcode   => SQLCODE,   
                  p_parms       =>    table_in   
                                   || ' '   
                                   || value1_in   
                                   || ' '   
                                   || value2_in); */  
   
            Raise_Application_Error (   
               -20000,   
               'Error processing request. Contact help desk with ID ' || l_id);   
         END;   
   END;   
END;

Package Body created.

Statement 12
Whenever you put dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL) inside a stored program unit, you should define it as AUTHID INVOKER. This means that when the program is executed, it runs with the privileges of the invoking, rather than defining/owning schema. And that means what the invoker can do is limited to its own security regime, and cannot piggy-back on the code owner's privileges.
More secure: Use Invoker Rights to Narrow Impact of Dynamic SQL
```
CREATE OR REPLACE PACKAGE dynamic_hr  
   AUTHID CURRENT_USER  
AS  
   PROCEDURE process_one_row (table_in    IN VARCHAR2,  
                              value1_in   IN VARCHAR2,  
                              value2_in   IN DATE);  
END; 
```
```
Package created.
```

Statement 13

Starting with Oracle Database 12c, you can set up whitelists for program units, specifying with other program units can invoke them. It is an easy, highly secure way to avoid unexpected invocations of your code.

More secure: Whitelist Access to Code

CREATE OR REPLACE PACKAGE dynamic_hr  
   AUTHID CURRENT_USER  
   ACCESSIBLE BY (public_facing_pkg)  
AS  
   PROCEDURE process_one_row (table_in    IN VARCHAR2,  
                              value1_in   IN VARCHAR2,  
                              value2_in   IN DATE);  
END;

Package created.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database