Re: Running stored procedures/Java code in oracle 8i on AIX

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 1 Jan 2003 02:10:44 -0800
Message-ID: <92eeeff0.0301010210.2c9ad558@posting.google.com>

"Computer Person" <xx_at_xx.com> wrote in message news:<2IoQ9.107898$yW.11205_at_news04.bloor.is.net.cable.rogers.com>...
> Hi,
>
> We have an application that has been written with a combination of Stored
> procedures and Java code (also in database). When the code is launched it
> apparently runs under the oracle userid so any files that the code
> manipulates has to be accessible to the oracle userid not the user who
> launched the process.
>
> What I am trying to understand is why the code uses the authority of the
> Unix oracle userid to read/write files. Second, now that we have recognized
> this behaviour we have made sure that the oracle userid can access the files
> but we found that the behaviour of the unix oracle security setup does not
> work correctly when files are accessed via stored procedures or java stored
> procedures.
>
> It is kind of hard to explain but I will try.. We have a filesystem, lets
> call it /data..Here is how it is setup at the AIX level:
>
> /data with owner jblow and group dba and perms rwxrwxr-x. The oracle user is
> setup with primary group of oinstall and groups=dba,oinstall.
>
> When a java program or stored procedure tries to update anything in the
> /data filesystem it gets a permission denied. If I open up the perms to
> rwxrwxrwx than it has no problems updating. The oracle userid (when used
> from a stored procedure) does not seem to be using the O/S security setup
> for the group. Since the filesystem and directory is owned by group dba and
> oracle has this group why would it not be able to update the files??
>
> When we logon directly from the AIX oracle userid and use vi or whatever to
> update files we have no problem in /data with perms rwxrwxr-x. This is
> pointing to the fact that under oracle stored procedures or java inside
> oracle the behaviour is showing a bug?
>
> Anyone seen this problem before. We are AIX 4.3.3 and Oracle 8.1.7 (8i)..
>
> THANKS!
Both Oracle and Java has built in security to prevent users from writing to the filesystems... Unless a particular directory is explicitly opened for use.

In Oracle PLSQL stored procedure, PLSQL can only write to directories opened by using utl_file_dir parameter in the init.ora file. If you do not have your directory listed in utl_file_dir then PLSQL will not be able to write to it.

In Java stored procedure, you have to set read/write file permissions for any java.io operations in Java's security policy. To do this look at DBMS_JAVA package documentation and also look at Java's security policy in java docs.
At minimum, you have to run these statements from sqlplus as SYS,

DBMS_JAVA.GRANT_PERMISSION('<Oracle user | Oracle Role>',
                           'SYS:java.io.FilePermission',
                           '<Your directory name>',
                           'read,write');
DBMS_JAVA.GRANT_PERMISSION('<Oracle user | Oracle Role>',
                           'SYS:java.lang.RuntimePermission',
                           'readFileDescriptor',
                           NULL);
DBMS_JAVA.GRANT_PERMISSION('<Oracle user | Oracle Role>',
                           'SYS:java.lang.RuntimePermission',
                           'writeFileDescriptor',
                           NULL);
DBMS_JAVA.GRANT_PERMISSION('<Oracle user | Oracle Role>',
                           'SYS:java.util.PropertyPermission',
                           '<Your directory name>',
                           'read,write');

COMMIT; Ofcourse, with the above permissions, you also need to have OS level permissions setup...meaning open the directory to Oracle for read/write.

Regards
/Rauf Sarwar Received on Wed Jan 01 2003 - 04:10:44 CST