2

I have created a PL/SQL a Java Source, and privileges have been granted.

The PL/SQL procedure is executing and no error is coming up. Within the JavaSource there is the following unix command:

ls -al > /orion/list/list.txt

the file List.txt is not being created within the directory.

How would i know the problem if no errors are coming up? Could this be a problem of rights being given to oracle from unix.

Oracle is on unix sun solaris

CRL88
  • 125
  • 4
  • 20

2 Answers2

3

From a distant memory, I am fairly certain you need to grant some privileges to the user executing the Java before it is allowed to execute unix commands.

Have a look at http://download.oracle.com/docs/cd/B28359_01/java.111/b31225/chten.htm

I think you need to give it java.io.FilePermission permission. One way to do this is to grant the role JAVASYSPRIV to your user. I have nowhere to test this out at the moment, but if that is not correct the link above should point you in the correct direction.

Stephen ODonnell
  • 4,441
  • 17
  • 19
  • The permission has been granted by a DBA The problem is that there are no errors being recorded on my Sql Navigator but the file list.txt is not being created within the directory – CRL88 Aug 22 '11 at 10:09
  • Ask your DBA to look in the alert logs to see if Oracle is reporting any issues. – Ollie Aug 22 '11 at 10:14
2

I concur with Stephen ODonnell.

I have implemented the exact same Java functionality (creating a file containing a directory listing) recently.

I needed to grant the following:

-- this grants read privilege on STDIN
EXEC dbms_java.grant_permission(
   grantee => '<username>', 
   permission_type => 'SYS:java.lang.RuntimePermission', 
   permission_name => 'readFileDescriptor', 
   permission_action => null
);

-- this grants write permission on STDOUT
EXEC dbms_java.grant_permission(
   grantee => '<username>', 
   permission_type => 'SYS:java.lang.RuntimePermission', 
   permission_name => 'writeFileDescriptor', 
   permission_action => null
);

-- this grants execute privilege for the 'ls' command
EXEC dbms_java.grant_permission(
   grantee => '<username>', 
   permission_type => 'SYS:java.io.FilePermission', 
   permission_name => '/bin/ls', 
   permission_action => 'execute'
);

-- this grants read, write, delete and execute on all 
-- of the referenced directories (subdirectories of <directory>)
EXEC dbms_java.grant_permission(
   grantee => '<username>', 
   permission_type => 'SYS:java.io.FilePermission', 
   permission_name => '<directory>/-', 
   permission_action => 'read,write,delete,execute'
);

-- this grants execute on sh
EXEC dbms_java.grant_permission(
   grantee => '<username>', 
   permission_type => 'SYS:java.io.FilePermission', 
   permission_name => '/bin/sh', 
   permission_action => 'read,execute' 
);

Hope this helps. Ollie.

Ollie
  • 17,058
  • 7
  • 48
  • 59
  • Yes, that code looks familiar - probably best to run it instead or relying on the ROLE I suggested, just incase the role doesn't give everything needed. – Stephen ODonnell Aug 22 '11 at 10:17
  • 1
    I have set the following permissions and still the problem persists. Could this be due to the Oracle user not being the same as the Unix user begin DBMS_JAVA.grant_permission ('ORION', 'java.io.FilePermission','TestHostCommand', 'read ,write, execute, delete'); end; begin DBMS_JAVA.grant_permission ('ORION', 'SYS:java.lang.RuntimePermission','writeFileDescriptor', ''); end; begin DBMS_JAVA.grant_permission ('ORION', 'SYS:java.lang.RuntimePermission','readFileDescriptor', ''); end; – CRL88 Aug 22 '11 at 10:19
  • "Could this be due to the Oracle user not being the same as the Unix user". No, the user in the database is seperate from the database that accesses the OS commands. Does Oracle have access permissions on the OS directories? – Ollie Aug 22 '11 at 10:22
  • 1
    so in this case that would be ORION cause i'm still new at this. Thanks for your patience – CRL88 Aug 22 '11 at 10:25
  • It would be Oracle that accesses the OS directory on behalf of the ORION user. In the case I have used, the Unix directories that Oracle had to access were all owned by the OS Oracle user so there were no OS directory permission issues. I also ensure that the files within the directory are either owned by the OS Oracle user or at least the Oracle user has permissions to manipulate them. – Ollie Aug 22 '11 at 10:30
  • Thanks how can i check for the Oracle User through UNIX if they have the right privileges and what priveleges do i have to look for? Or need to be given to Oracle – CRL88 Aug 22 '11 at 10:35
  • I'd ask your Unix sysadmin (or at least your DBA) to check for you. I'm no unix expert so I can't direct you to look in the unix groups and how the security model works etc. but one of the two roles I mentioned should be able to help you out. – Ollie Aug 22 '11 at 10:37
  • Thanks i'll ask the DBA to check for permissions. – CRL88 Aug 22 '11 at 11:36