18.5.08

To b:\ or not to B:\

Recently, I ran into a small issue with Java permissions. Starting point is the DirList Java procedure to list the content of an OS directory. Here is the code to get started (executed as SCOTT):

CREATE global TEMPORARY TABLE DIR_LIST ( filename VARCHAR2(255) ) ON
COMMIT
DELETE rows
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirList"
AS
import java.io.*;
import java.sql.*;
public class DirList {
public static void getList(String directory) throws SQLException {
File path = NEW File( directory );
String[]
list = path.list();
String element;
for(INT i = 0; i < list.length; i++) {
element = list[i];
#sql { INSERT INTO DIR_LIST (FILENAME) VALUES (:element) };
}
}
}
/

CREATE OR REPLACE PROCEDURE get_dir_list ( p_directory IN VARCHAR2 )
AS language java name 'DirList.getList( java.lang.String )';
/

Don't grant the role JAVAUSERPRIV but use a more granular option. Grant read permission to SCOTT on the d: drive:

EXECUTE dbms_java.grant_permission( 'SCOTT', 'java.io.FilePermission','d:\','read' );

Everything is in place now, time to run the procedure:

EXECUTE get_dir_list('D:\');
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException:
the Permission (java.io.FilePermission D:\ read)
has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(SCOTT|PolicyTableProxy(SCOTT))
ORA-06512: at "SCOTT.GET_DIR_LIST", line 0
ORA-06512: at line 2

An error occurred, perhaps the directory did not exists? Executing a dir D:\ at the command prompt on the database server lists the files. The command prompt is not case sensitive, executing a dir d:\ returns the same listing. Perhaps but DBMS_JAVA.GRANT_PERMISSION is case sensitive?

EXECUTE get_dir_list( 'd:\' );
PL/SQL procedure successfully completed

SELECT * FROM DIR_LIST
/
FILENAME
--------------------------------------------------------------------------------
oracle
RECYCLER
System Volume Information

No comments: