• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2350
  • Last Modified:

Oracle: how to delete files on the Unix file system, if these files have 644 permission and oracle user is not owner?

Using the stored procedure called "rc" (see attached code), I am able to execute Unix commands from PL/SQL, for example:

SQL> exec rc ('touch /home/sde20/WORK_DIR/text_test_file.txt');

With the previous command, I can create an empty text file in the Unix directory /home/sde20/WORK_DIR

Well, of course I can also use the rm command to remove files from Unix, and here is the problem!

Please suppose that the Unix user related to Oracle is called oracle10; in the previous example, the text file has the following permissions:

-rw-r--r--   1 sde20      users       463538 Oct 18 18:20 00203C_17062007_1113_AggregatoDistributore.csv
-rw-r--r--   1 sde20      users       463538 Oct 18 18:20 00203C_17062008_1113_AggregatoDistributore.csv
-rw-r--r--   1 sde20      users       463538 Oct 18 18:20 00202E_17062008_1116_AggregatoDistributore.csv
-rw-r--r--   1 sde20      users       463538 Oct 18 18:20 00202F_17102008_2359_AggregatoDistributore.csv
-rw-r--r--   1 sde20      users       463538 Oct 18 18:20 00202F_17102008_2259_AggregatoDistributore.csv
-rw-r--r--   1 sde20      users       463538 Oct 18 18:20 00202F_17102008_2159_AggregatoDistributore.csv
-rw-rw-rw-   1 oracle10   users            0 Oct 20 13:54 text_test_file.txt

You can see that the file called text_test_file.txt has rw-rw-rw permissions, and oracle10 is the owner.

I have problems to delete the OTHER files, owned by sde20 Unix user.

I tried with:

exec rc ('touch /home/sde20/WORK_DIR/00202F_17102008_2159_AggregatoDistributore.csv');

in order to empty them, without success: the files whose owner is different from oracle10, remain unchanged :-(

How can I fix this?

Please note that the files owned by sde20 user have 500Kbytes dimension, so I would like to avoid the classical disk full error, or over quota error.

I should be grateful with 500 points, to the best suggestion to solve this problem.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED NE."Util" as import java.io.*;
  import java.lang.*;
  
  public class Util extends Object
  {  
    public static int RunThis(String args)
    {  
    Runtime rt = Runtime.getRuntime();
    int        rc = -1;
  
    try
    {  
       Process p = rt.exec(args);
  
       int bufSize = 4096;
       BufferedInputStream bis =
        new BufferedInputStream(p.getInputStream(), bufSize);
       int len;
       byte buffer[] = new byte[bufSize];
  
       // Echo back what the program spit out
       while ((len = bis.read(buffer, 0, bufSize)) != -1)
          System.out.write(buffer, 0, len);
  
       rc = p.waitFor();
    }  
    catch (Exception e) 
    {  
       e.printStackTrace();
       rc = -1;
    }  
    finally
    {  
       return rc;
    }  
    }  
  }
/
 
CREATE OR REPLACE FUNCTION ne.run_cmd (p_cmd IN VARCHAR2)
   RETURN NUMBER
AS
   LANGUAGE JAVA
   NAME 'Util.RunThis(java.lang.String) return integer';
/
 
CREATE OR REPLACE PROCEDURE ne.rc (p_cmd IN VARCHAR2)
AS
   x   NUMBER;
BEGIN
   x := run_cmd (p_cmd);
END;
/

Open in new window

0
CRISTIANO_CORRADI
Asked:
CRISTIANO_CORRADI
  • 13
  • 8
  • 3
  • +1
1 Solution
 
grahamcorpCommented:
So I am clear, you want to delete a file that you don't have permissions on it to do?!
0
 
grahamcorpCommented:
The command...
'touch /home/sde20/WORK_DIR/00202F_17102008_2159_AggregatoDistributore.csv'
won't work as you only have read permissions on that file for the user you are running your code as, i.e. Oracle10 - you can't get round this, unless you can change the permissions on the file or change to the owner of the file.
0
 
CRISTIANO_CORRADIAuthor Commented:
Please suppose I have the Unix root password to do it, but the call to remove the files has be lauched from Oracle PL/SQL; you can use my rc stored procedure, if you find it useful.
Other workaround will be appreciated.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
grahamcorpCommented:
Apologies to keep creating seperate posts, but I am new and not sure if i can edit previous ones.  If I have understood your problem, really there is nothing that I can think to do, other than the two things above.  The whole point of file permissions is so people can't go around servers deleting files they don't have permissions to!
0
 
grahamcorpCommented:
Then why not do an su as part of the command?
0
 
grahamcorpCommented:
Sorry ignore the previous comment su won't work in this case!
0
 
aaronblumCommented:
Can you set the permission on the sde20 files to 664 when they are created?  Otherwise, perhaps you can create a script as root that can then be called by the oracle user to re-permission the offending files.
0
 
grahamcorpCommented:
Have you thought about doing this either a) using the 'sudo' command, or b) (which may also involve a, creating a script that can be run on the server itself to make the file permission change and then calling this from rc?
0
 
CRISTIANO_CORRADIAuthor Commented:
I cannot create script on the server :-(

I have only to solve this problem from Oracle PL/SQL, having the necessary password on Unix.
0
 
grahamcorpCommented:
Can u use sudo?
0
 
CRISTIANO_CORRADIAuthor Commented:
$ man sudo
No manual entry for sudo.
$ sudo
ksh: sudo:  not found
$
0
 
aaronblumCommented:
try /sbin/sudo
0
 
CRISTIANO_CORRADIAuthor Commented:
$ /sbin/sudo
ksh: /sbin/sudo:  not found
$

Post Scriptum: I operate on HP-UX environment.
0
 
grahamcorpCommented:
U most likely don't have it installed, can you get it installed?
0
 
CRISTIANO_CORRADIAuthor Commented:
No, because in production environment this request will not be allowed.
0
 
grahamcorpCommented:
I am not sure then it will be possible, if you are limited to doing this within PL/SQL and can't install any other scripts or programs, then the only two ways I know to do this are su or sudo.
Su will prompt for a password, so thats out, sudo you don't have, so thats out.  I guess it depends if they really want this or not!
0
 
CRISTIANO_CORRADIAuthor Commented:
Okay, if there is no solution to my problem, I'll wait one day more, and then I'll assign points.
0
 
grahamcorpCommented:
U may want to have a read here, it seems to be suggesting that it may be possible through and input and output stream, though i am not so sure on that!
0
 
aaronblumCommented:
It is possible through I/O stream but ridiculously insecure as you will be sending passwords essentially plan text across a network.  If I am not mistaken, you'll be sending your root password unsecured as well.  If you would like to still try this then the link from grahamcorp will certainly get you on your way.
0
 
grahamcorpCommented:
Yes I am not sure i would want to do it that way either, really sudo would be far better!
I suppose if you are running on a lan and not in a DMZ or anything like that, i.e. you are happy with your password being thrown around the network then fine - however I suspect your admin guys wouldn't be if you made them aware this was the alternative!
0
 
CRISTIANO_CORRADIAuthor Commented:
You're right, aaronblum: sending my root password across my network is unsecure....!

0
 
CRISTIANO_CORRADIAuthor Commented:
Yes, grahamcorp, I'm running on a LAN.
0
 
grahamcorpCommented:
Then its really your call Cristiano, but i would likely flag this up as a security risk, lan or not, if there not likely to install sudo on the servers then i can't see them being too happy with the password floating around, but your better letting them make the call on this one.
0
 
johnsoneSenior Oracle DBACommented:
Deleting a file on a UNIX server actually has nothing to do with the permission on the file itself.  It has to do with the directory permission the file resides in.  If you have write permission to the directory, then you can delete the file, regardless of who owns the file or what the permissions are.  If you try to delete a file using the rm command, it will prompt you, but you can still delete it ("rm -f" gets around the prompting).

If you cannot get write permission on the directory and you cannot create a script or executable on the server, I am afraid you may be stuck.  If you could create a script or executable, you could use the sticky bits to have them run as a different user and get around the permissions.
0
 
grahamcorpCommented:
Actually thats a good point johnsone.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 13
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now