UTL_FILE and Security 9.2

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 20 Feb 2004 12:35:40 -0000
Message-ID: <CFECD6EA683C524BB5FD22C0F1F34ACB06B129@bristol43.audit-commission.gov.uk>

I seem to have spent this week doing some investigation into UTL_FILE

One of the oddities I came across, which is sort of documented in metalink note 196939.1 and a tar I raised.
>From 9.2 you can use directory objects instead of UTL_FILE_DIR in init.ora. There are 2 available permissions on directory objects which you can grant to your users READ and WRITE. they do not do what one might expect. In particular granting READ will allow the user to WRITE to that directory (assuming that the account that is running oracle has the necessary os permissions) . granting WRITE has no discernible effect at all, it certainly doesn't allow WRITES. The explanation given to me by my support analyst, which I can sort of believe but strikes me as coming from the department of WTF, is that WRITE only applies to the external agent used for external tables, in otherwords you need the WRITE priv only for the puposes of logging of discards etc for external tables. This is as they say of the Monty Hall problem 'deeply counterintuitive'.

from 196939.1

UTL_FILE won't attempt to check for permission before executing an open/read/write/delete request. We expect that the operating system will deny the request where appropriate. UTL_FILE will blindly issue any action requested and look for success or failure return status from the operating system.

and
the DBA can control directory access by either (1) creating separate

directories for users with differing access requirements, or (2) use operating system utilities and features for controling read and write access by users.

Option 1 seems to me in fact to be the only way to go, since AFAIK the os request is actually executed in the context of the oracle/system os account. I can't see how option 2 could be implemented even with externally identified users.

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 20 2004 - 06:35:40 CST