MacLochlainns Weblog

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read');
  6  END;
  7  /

After you grant privileges to read, write, and delete files with Oracle’s dbms_java package, you must disconnect any active session to enjoy the new privileges. If you forget to disconnect and re-connect an active session, you get the following error message:

FROM     TABLE(list_files(get_directory_path('STAGE')))
               *
ERROR AT line 2:
ORA-29532: JAVA CALL terminated BY uncaught JAVA EXCEPTION:
JAVA.security.AccessControlException: DIRECTORY permissions restricted.

The problem and fix are simple. The permissions aren’t in effect until after you disconnect and reconnect your active sessions. I put this there because when I Google’d it, there wasn’t an answer already.

While I’m on the topic of privilege errors, that aren’t really errors, I thought it would be helpful to qualify how the delete permission works because I couldn’t find it anywhere in the Oracle documentation (if somebody finds it let make a comment that enriches the discussion). Unlike the read and write permissions, the delete permission requires permissions on specific files.

If you only grant permission on like:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read,write,delete');
  6  END;
  7  /

You would get this message when trying to delete an external file:

BEGIN
*
ERROR AT line 1:
ORA-29532: JAVA CALL terminated BY uncaught JAVA EXCEPTION:
JAVA.security.AccessControlException: the Permission (JAVA.io.FilePermission
c:\data\LOG\item_import.LOG read) has NOT been granted TO IMPORTER. The PL/SQL
TO GRANT this IS dbms_java.grant_permission( 'IMPORTER',
'SYS:java.io.FilePermission', 'c:\data\log\item_import.log', 'read' )
ORA-06512: AT "IMPORTER.DELETE_FILE", line 1
ORA-06512: AT line 2

It requires that you grant permissions on individual files to have the privilege of deleting them from within Oracle. That grant would look like the following:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload\item_import.log'
  5                               ,'read,write,delete');
  6  END;
  7  /

As always, I hope this helps other developers.