Can oracle read local files when only associated by secondary group?

Question

I am trying to run a very simple python script from Oracle. Oracle is on the same linux box as the script. It opens a file and creates a checksum. It is triggered by a 'recon' user within oracle.

Running the script from within Oracle works fine as long as the file owner is 'oracle', or the group is 'oinstall' (oracle's default group), or the public is set to rx, the script works.

The problem is that we must use a different user:group, and we cannot use public permissions. We added the oracle user to the file's group.

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),202175(efs_data)

When running from within Oracle as we did before, it now fails, however, when sudo'ing into the oracle user and running the script directly, it works so we know the linux permissions are ok.

What could cause this? I guess Oracle is doing some other sort of access check overlaying the linux permissions, and this ignores the secondary groups and looks at gid only.

as 'recon' schema:

set serveroutput on size unlimited
declare
 x number;
begin
 x := run_cmd('/home/oracle/bin_dir/pytest.py');
 dbms_output.put_line('return:' || x);
end;

run_cmd:

create or replace function RUN_CMD( p_cmd  in varchar2) return number as
language java
name 'Util.RunThis(java.lang.String) return integer';

Util.RunThis:

import java.io.*;
  import java.lang.*;

  public class Util extends Object
  {

    public static int RunThis(java.lang.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;
    }
  }
}

/home/oracle/bin_dir/pytest.py:

#! /usr/bin/python -W ignore::DeprecationWarning
import paramiko
import logging
import datetime
import pwd
import md5
import os

def test_file_open(local_file):
  print 'Trying to open: '+ local_file
  logging.info('Trying to open: ' + local_file)
  local_file_data = open(local_file, "rb").read()
  checksum = md5.new(local_file_data).hexdigest()
  return checksum

def main():
  logging.basicConfig(filename='/mounts/users/dmz/pytest.log', level=logging.INFO)
  logging.info('==========================================')
  logging.info('START: ' + str(datetime.datetime.now()))
  logging.info('getuid: ' + pwd.getpwuid( os.getuid() ).pw_name)
  logging.info('geteuid: ' + pwd.getpwuid( os.geteuid() ).pw_name)

  checksum = test_file_open('/test.txt')

  print 'Success!, checksum: ' + checksum
  logging.info('Success! checksum: ' + checksum)
  logging.info('END: ' + str(datetime.datetime.now()))

if __name__ == '__main__':
  main()

Output (with oracle as file owner):

-rwxrwx---. 1 oracle efs_data 0 Jun  7 19:56 /test.txt

INFO:root:==========================================
INFO:root:START: 2018-06-07 19:45:32.005429
INFO:root:getuid: oracle
INFO:root:geteuid: oracle
INFO:root:Trying to open: /test.txt
INFO:root:Success! checksum: 9f1e1404fd72b59121d45a8beb4dab5d
INFO:root:END: 2018-06-07 19:45:32.007078

Output (with permissions only via group association):

-rwxrwx---. 1 root efs_data 0 Jun  7 19:57 /test.txt

INFO:root:==========================================
INFO:root:START: 2018-06-07 19:44:15.748559
INFO:root:getuid: oracle
INFO:root:geteuid: oracle
INFO:root:Trying to open: /test.txt

Just checking... have you told Oracle to allow Java to read the "/test" directory, by running `exec dbms_java.grant_permission('YOUR_ORACLE_USER_NAME','java.io.FilePermission', '/test/*', 'read');`? — Matthew McPeak, Jun 11 '18 at 19:25
@MatthewMcPeak We have. Also write and execute for testing purposes. Thanks for the interest. — Paul, Jun 12 '18 at 07:59
@Paul - As far as I see, the first time (user oracle) all works fine as you have read/write/execute rights on the file. The other case - using user root, the file is actually *owned* by the root user and the file has RWE permissions for the owner and the users of the group of root. If oracle is a user, which is in one of the groups of the root, then you'll have these privileges over the file. In your case (file owned by root), the user oracle (which the Database uses), wil ltry to access it with the permissions of the "other users", which are *none* (-rwxrwx--- the last --- indicate no perm.). — g00dy, Jun 12 '18 at 09:07
To make sure if gid only is used, you could check if you set the file's group to `oinstall` — J. Chomel, Jun 12 '18 at 13:15
Yep, setting file group to oinstall works. @g00dy oracle user is part of the efs_data group, so should have permission (and does, if I sudo to oracle and read the file). — Paul, Jun 12 '18 at 13:39

score 4 · Accepted Answer · 2018-06-14T07:09:30.900

4

I have a similar problem with DIRECTORY and external tables where linux group access is seemingly ignored. I was able to solve by using an acl and letting the oracle user have the rights it needs while letting the ownership of the file stay will another user.

ll test.txt
-rwx------. 1 lunc users 940 Jun 13 09:34 test.txt

setfacl -m u:oracle:rwx test.txt

getfacl test.txt

# file: test.txt
# owner: lunc
# group: users
user::rwx
user:oracle:rwx
group::---
mask::rwx
other::---

ll test.txt
-rwxrwx---+ 1 lunc users 940 Jun 13 09:34 test.txt

Oracle accepts this (at least for external tables) and is able to access the file.

edited Jun 14 '18 at 07:09

answered Jun 13 '18 at 09:09

Thank you Lunc, this helped! It's annoying we're forced to mess around, but it's a solution at least. – Paul Jun 18 '18 at 08:04
Similarly, we also used smb mount and forced the uid to be the oracle user. This also works whilst the underlying permissions are as before. – Paul Jul 02 '18 at 07:20

Can oracle read local files when only associated by secondary group?

1 Answers1