file handling from PLSQL

All posts relating to Oracle database administration.

Moderator: Tim...

file handling from PLSQL

Postby Srivalli » Fri Mar 23, 2012 4:28 pm

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
Thanks.
Srivalli
Member
 
Posts: 26
Joined: Fri Feb 11, 2011 10:35 pm

Re: file handling from PLSQL

Postby Tim... » Fri Mar 23, 2012 4:31 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: file handling from PLSQL

Postby Srivalli » Fri Mar 23, 2012 7:43 pm

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
Thanks.
Srivalli
Member
 
Posts: 26
Joined: Fri Feb 11, 2011 10:35 pm

Re: file handling from PLSQL

Postby Srivalli » Fri Mar 23, 2012 7:46 pm

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
Thanks.
Srivalli
Member
 
Posts: 26
Joined: Fri Feb 11, 2011 10:35 pm

Re: file handling from PLSQL

Postby Tim... » Fri Mar 23, 2012 8:05 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: file handling from PLSQL

Postby Tim... » Fri Mar 23, 2012 8:16 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: file handling from PLSQL

Postby Srivalli » Thu Apr 12, 2012 5:16 pm

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 :)
Thanks.
Srivalli
Member
 
Posts: 26
Joined: Fri Feb 11, 2011 10:35 pm

Re: file handling from PLSQL

Postby Tim... » Thu Apr 12, 2012 6:08 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: file handling from PLSQL

Postby boldmoves » Tue Feb 12, 2013 8:15 pm

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.
boldmoves
Member
 
Posts: 1
Joined: Tue Feb 12, 2013 8:04 pm

Re: file handling from PLSQL

Postby Tim... » Tue Feb 12, 2013 8:26 pm

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 9 guests