file handling from PLSQL

[Srivalli]

Hello Tim,

I have granted
dbms_java.grant_permission(<Schema_name>,'SYS:java.io.FilePermission','/home/oracle/TestDir1/','read,write,execute,delete');

The schema is able to access the directory specified i.e /home/oracle/TestDir1
But it is not able to create any folder from that level.

May I know as a DBA what permission I've to grant so that the schema will be able to create any folder from that directory level onwards ?

Thanks & Regards,
Sri

[Tim...]

Hi.

Does it work correctly if you allow the schema to access any file?

Code: Select all

EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');

Cheers

Tim...

[Srivalli]

Hello Tim,

I have already executed that. But the problem is the procedure is not able to create any folder under /home/oracle/TestDir1

The package is making use of a function like below to make a directory:
FUNCTION mkdir (p_path IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'FileHandler.mkdir (java.lang.String) return java.lang.int';

I just want to find out what privilege needs to be granted to the schema other than
dbms_java.grant_permission(<Schema_name>,'SYS:java.io.FilePermission','/home/oracle/TestDir1/','read,write,execute,delete');

so that it'll be able to create any folder under it like /home/oracle/TestDir1/test2 (OR) /home/oracle/TestDir1/test2/test3

Thanks,
Sri

[Srivalli]

Just want to more clear....

the schema is able to access /home/oracle/TestDir1 as the above privilege has been granted. But it is not able to create any folders under that level like /home/oracle/TestDir1/test2 (OR) /home/oracle/TestDir1/test2/test3

[Tim...]

Hi.

I just do what it says to do in the article.

http://www.oracle-base.com/articles/8i/ ... mPLSQL.php

My schema is called TEST, so I run the following.

Code: Select all

EXEC DBMS_JAVA.grant_permission('TEST', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
EXEC DBMS_JAVA.grant_permission('TEST', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('TEST', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
GRANT JAVAUSERPRIV TO TEST;

If I run this command, the directory is created.

Code: Select all

DECLARE
  l_number NUMBER;
BEGIN
  l_number := File_API.mkdir ('/tmp/my_new_dir');
END;
/

It works. Just like the article says.

Cheers

Tim...

[Tim...]

Your issue is you are trying to grant permissions on a specific directory. That is fine if you just want to read and write files in that directory, but you will not be allowed to create a new subdirectory. Why? Because you don't have permission on that subdirectory. To do that you would first have to grant the JVM permission to do access the new subdirectory, then create it.

For example:

Code: Select all

dbms_java.grant_permission(<Schema_name>,'SYS:java.io.FilePermission','/home/oracle/TestDir1/test1','read,write,execute,delete');
dbms_java.grant_permission(<Schema_name>,'SYS:java.io.FilePermission','/home/oracle/TestDir1/test2','read,write,execute,delete');
dbms_java.grant_permission(<Schema_name>,'SYS:java.io.FilePermission','/home/oracle/TestDir1/test2/test3','read,write,execute,delete');

Then create them...

That's why I asked if you had tried this and asked if it had worked.

Code: Select all

EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');

Unfortunately, you never bothered to tell me the answer, thus delaying the resolution to your issue!

So, if you want to grant for specific directories, you will also need to grant for any potential subdirectories you want to create in advance of creating them, which is crappy.

Cheers

Tim...

[Srivalli]

Hi Tim,

sorry about that.
Yes, it worked when I granted the <<ALL FILES>> privilege.

Your article on file handling from PLSQL is really awesome!!.......Thanks a ton to you from all of us

[Tim...]

[boldmoves]

Hi Tim,

I have an Oracle DB installed on a Windows XP machine and I tried your code and it works like a charm.

The one issue I have is that I can't access Mapped Network Drives with the code.

Is there something I can do to be able to access Mapped Network Drives?

Thanks,

Newton.

[Tim...]

Hi.

Have you tried using a UNC path, rather than a drive letter?

Cheers

Tim...