Pete Finnigan's Oracle security weblog

Dirk Nachbar says:

Hi Pete,

Paul's paper is really interesting, but there some small problems:

1. As Paul declare in his paper it should also work on UNIX platforms is not correct. At first the Options Flag '-R' in the external table definition can't be used. Secondly the PL/SQL Block to create the shellscript are creating a correct shellscript, but not with executable rights :-( So it's not working.

2. On Windows it's working only if add in the creation of the batch-file some environment variables: set ORACLE_HOME=xxx / set ORACLE_SID=xxx / set PATH=%ORACLE_HOME%\bin;%PATH% and then the batchfile is working correctly. The same environments you should also set on the UNIX platforms, but then still the problem with the executable permissions on the shellscript is existing.

Cheers

Dirk

Pete says:

Hi Dirk,

Thanks for your comment. I hope that Paul will drop by and answer your points himself.

cheers

Pete

Paul M. Wright says:

Hi Dirk,
Point 1. About -R not working ~ please refer to line 1 of the last page which states "-R is just to get the external table to work". In other words you need to have an OPTION for the external table to work. It does not actually get used in this case. Did you test the code without a specified OPTION?
2. With regards paths ~ all paths can be specified as absolute as the script runs with the privileges of the oracle user. I tested the scripts thoroughly and they all worked fine for me. Try using absolute paths if you have problems with env vars.
3. Regarding execute unix os permission~ the top of the penultimate page states "--Note that even though the contents of the previously existing shell script on the OS have been
completely overwritten the OS file execute privileges stay the same on the new version of the script." the file is called previously_existing.sh . There will probably be an executable file in the OS directory if the DB directory is made executable as that is the point of doing so. Just need to list the contents as mentioned.
But do remember that the file does not need to be executable on Windows thus making this a more serious issue on Windows.

The point of this paper is that granting execute on a directory to a user with create table is effectively granting osdba because once the script is on the os it acts with oracle's privs, thus bypassing all DB directory restrictions. This makes revoking public execute on utl_file a must and should be done by default given the new directory execute permission in 11g. Everyone I know agrees on this point. So I am confident about the validity of the argument, but it is all the better for public scrutiny. Thanks for your feedback Dirk.
Cheers,
Paul