Solved

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

Posted on 2008-10-20
25
2,244 Views
Last Modified: 2013-12-18
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
Comment
Question by:CRISTIANO_CORRADI
  • 13
  • 8
  • 3
  • +1
25 Comments
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22756828
So I am clear, you want to delete a file that you don't have permissions on it to do?!
0
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22756848
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
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22756857
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
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22756864
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
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22756872
Then why not do an su as part of the command?
0
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22756882
Sorry ignore the previous comment su won't work in this case!
0
 
LVL 2

Expert Comment

by:aaronblum
ID: 22756934
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
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22756936
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
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22757021
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
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22757041
Can u use sudo?
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22757064
$ man sudo
No manual entry for sudo.
$ sudo
ksh: sudo:  not found
$
0
 
LVL 2

Expert Comment

by:aaronblum
ID: 22757074
try /sbin/sudo
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:CRISTIANO_CORRADI
ID: 22757084
$ /sbin/sudo
ksh: /sbin/sudo:  not found
$

Post Scriptum: I operate on HP-UX environment.
0
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22757093
U most likely don't have it installed, can you get it installed?
0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22757105
No, because in production environment this request will not be allowed.
0
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22757123
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
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22757136
Okay, if there is no solution to my problem, I'll wait one day more, and then I'll assign points.
0
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22757231
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
 
LVL 2

Expert Comment

by:aaronblum
ID: 22757350
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
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22757376
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
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22757383
You're right, aaronblum: sending my root password across my network is unsecure....!

0
 

Author Comment

by:CRISTIANO_CORRADI
ID: 22757392
Yes, grahamcorp, I'm running on a LAN.
0
 
LVL 1

Expert Comment

by:grahamcorp
ID: 22757437
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
 
LVL 34

Expert Comment

by:johnsone
ID: 22758490
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
 
LVL 1

Accepted Solution

by:
grahamcorp earned 500 total points
ID: 22758551
Actually thats a good point johnsone.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now