How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?

Question

I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf ) Unfortunately, this feature, which seems quite useful in our lounge seems very dangerous, as someone with access to the OS (or remotely....) could exploit it to cause the database to get compromised, or even worst - the whole OS.

I have restricted the access to this feature to the minimum, and revoked any additional privileges which might allow outside access to the os (extproc, java, etc)

However, there are still times when we must use this feature, and this is where I ask you guys 2 main questions:

1. How do you protect against malicious attacks using this wicked feature ?
2. Assuming something has failed in the security mechanisms, what sort of ways are there to detect that someone used this feature in an evil way? What sort of queries (or content of them) could be seen ?

Thanks (:

Answer 1

Oracle has had the capability of executing external code from SQL for a long time - EXTPROCs, data cartridges, and so on. You say

someone with access to the OS (or remotely....) could exploit it to cause the database to get compromised

But what does this even mean? Someone with access to the OS as the oracle user can access your DBFs directly (they're just files on the disk), can attach directly to the SGA, can make a backup and copy it off, can snoop the network traffic (as root). In the case of a malicious developer, the can do whatever they want in PL/SQL and wrap it. I don't see how you are introducing a new vulnerability by using this feature. If it makes your job or your user's jobs easier, go for it.

Answer 2

If a user has access to the OS and the the folder containing the PREPROCESSOR program then theoretically they can do anything the Oracle OS user can do.

1. Prevent this by preventing this level of access to the OS.

2. Monitor this by monitoring access to the OS and to the folder.

Answer 3

As mentioned there are two main vectors of compromise:

1. OS Level compromise of the database.

   Mitigation:

   • Use the standard OS features, limitation by privilege, but a lot of files need to be protected.
   • Stop normal users from logging on, only allow access via sqlnet (recommended).

2. SQLNet / SQL compromise of the database.

   Mitigation:

   • Limited privilege schemas used for web application (another user is the owner of the tables and grants specific access to the web app users).
   • Use packages to grant specific access to the features of the database with checks for misuse.