Most likely scenario is that since under role separation listener runs as grid user and it appears certain commands are getting executed as grid user. Which muddies the "role separation" when grid user is involved with database objects. Whether this is a bug or not is not confirmed yet, SR is on going.
Below is the test case to recreate the behavior.
1. Create a directory to hold the external table related files in a location that could be made common for both grid and oracle user. In this case /usr/local is chosen as the location.
[root@rhel6m1 local]# cd /usr/local/ [root@rhel6m1 local]# mkdir exdata [root@rhel6m1 local]# chmod 770 exdata [root@rhel6m1 local]# chown oracle:oinstall exdataDirectory permission are set 770 for the first set of test cases.
2. The external table is created using a shell script and using the preprocessor clause (this external table example is available in Oracle Magazine 2012 Nov, if memory is correct!). Create the shell script file that will be the basis for the external table with following content
[oracle@rhel6m1 exdata]$ more run_df.sh #!/bin/sh /bin/df -Pl3. Create a directory object and grant read,write permission on the directory object to the user that will create the external table
SQL> create or replace directory exec_dir as '/usr/local/exdata'; SQL> grant read,write on directory exec_dir to asanga;Check the user has the privileges on the directory
SQL> select * from dba_tab_privs where table_name='EXEC_DIR'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE -------- ----- ---------- -------- ---------- --- --- ASANGA SYS EXEC_DIR SYS READ NO NO ASANGA SYS EXEC_DIR SYS WRITE NO NO4. Create the external table
SQL> conn asanga/asa
CREATE TABLE "DF"
( "FSNAME" VARCHAR2(100),
"BLOCKS" NUMBER,
"USED" NUMBER,
"AVAIL" NUMBER,
"CAPACITY" VARCHAR2(10),
"MOUNT" VARCHAR2(100)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "EXEC_DIR"
ACCESS PARAMETERS
( records delimited by newline
preprocessor exec_dir:'run_df.sh'
skip 1 fields terminated by whitespace ldrtrim
)
LOCATION
( "EXEC_DIR":'run_df.sh' ));5. Case 1. Only oracle user having permission on the external table file (run_df.sh).Set permission on the external file so that only oracle user has read and execute permission and no other permissions set on the file
[oracle@rhel6m1 exdata]$ chmod 500 run_df.sh [oracle@rhel6m1 exdata]$ ls -l run_df.sh -r-x------. 1 oracle oinstall 22 Oct 9 14:01 run_df.shTest 1.1 Run select query on the external table connecting with a local connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa SQL> select * from df; FSNAME BLOCKS USED AVAIL CAPACITY MOUNT ---------- ---------- ---------- ---------- ---------- ---------- /dev/sda3 37054144 32706324 2465564 93% / tmpfs 1961580 228652 1732928 12% /dev/shm /dev/sda1 99150 27725 66305 30% /bootTest 1.2 Run select query on the external table connecting with a remote connection. In this case an SQLPlus remote connection is used. Output is the same even if a JDBC connection is used.
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from df; select * from df * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error KUP-04095: preprocessor command /usr/local/exdata/run_df.sh encountered error "/bin/sh: /usr/local/exdata/run_df.sh: Permission deniedAs seen from the above output no rows returned and an error occurs. Interesting line is "/usr/local/exdata/run_df.sh: Permission denied" no such permission issue when executing with a local connection! Same table,same query and different outputs depending on whether connection is local or remote.
6. Case 2 Oracle user having execute and group having read permission.
All else remaining the same change the permission of the external file to 140 so oracle user has execute permission and group (oinstall) has read permission
[oracle@rhel6m1 exdata]$ chmod 140 run_df.sh [oracle@rhel6m1 exdata]$ ls -l run_df.sh ---xr-----. 1 oracle oinstall 22 Oct 9 14:01 run_df.shTest 2.1 Run select query with a local connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa SQL> select * from df; select * from df * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error KUP-04095: preprocessor command /usr/local/exdata/run_df.sh encountered error "/bin/sh: /usr/local/exdata/run_df.sh: Permission deniedThis time running with a local connection fails. Same error as before.
Test 2.2 Run select query with a remote connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from df; FSNAME BLOCKS USED AVAIL CAPACITY MOUNT ---------- ---------- ---------- ---------- ---------- ---------- /dev/sda3 37054144 32697924 2473964 93% / tmpfs 1961580 228652 1732928 12% /dev/shm /dev/sda1 99150 27725 66305 30% /bootRunning with a remote connection succeeds. Again same table,same query and different outputs depending on whether connection is local or remote. Looking at the file permission it is clear that when connection is remote file is read by a user that belongs to the oinstall group and executed by Oracle user. Only other user in the oinstall group beside oracle is grid. As remote connection comes in via the listener which is running as grid user remote connections are able to read the external file.
7. Case 3 Oracle user having read and execute permission and group having read permission.
This is a amalgamation of the permission from the above cases. Change external file permission so oracle user has read and execute permission and group has read permission.
[oracle@rhel6m1 exdata]$ chmod 540 run_df.sh [oracle@rhel6m1 exdata]$ ls -l run_df.sh -r-xr-----. 1 oracle dba 22 Oct 9 14:01 run_df.shTest 3.1 Run select query with a local connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa SQL> select * from df; FSNAME BLOCKS USED AVAIL CAPACITY MOUNT ---------- ---------- ---------- ---------- ---------- ---------- /dev/sda3 37054144 32706324 2465564 93% / tmpfs 1961580 228652 1732928 12% /dev/shm /dev/sda1 99150 27725 66305 30% /bootTest 3.2 Run select query with a remote connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from df; FSNAME BLOCKS USED AVAIL CAPACITY MOUNT ---------- ---------- ---------- ---------- ---------- ---------- /dev/sda3 37054144 32697924 2473964 93% / tmpfs 1961580 228652 1732928 12% /dev/shm /dev/sda1 99150 27725 66305 30% /bootThis time both local and remote connections returns rows. So when a external table is created with a preprocessor to get same behavior for both local and remote connections permission must be set for both Oracle user and group.
If the external table is created without a preprocessor but using a static data file then permission of the file makes no difference to the output whether the connection is local or remote. However in this case it's the permission of the directory that matters and this directory permission also applicable to the preprocessor cases mentioned above as well.
8. A static file containing comma separated list of values will be created with the following command where it extract information about file permission and ownership.Execute it in a folder with several files
for i in `ls`; do ls -l $i | awk '{print $1","$3","$4","$9}' >> permission.txt ; done
[oracle@rhel6m1 exdata]$ more permission.txt
-rw-r--r--.,oracle,asmadmin,DF_27539.log
-rw-r--r--.,oracle,asmadmin,DF_27545.log
-rw-r--r--.,oracle,asmadmin,FILE_PERMS_27818.log
-rw-r--r--.,oracle,asmadmin,FILE_PERMS_27842.log
-r-xr-----.,oracle,dba,run_df.sh
-rwxr-xr-x.,root,root,status.sh
-rwxr-xr-x.,root,root,t.shCopy the generated file to exdata directory created in the previous cases. Set the permission of the permission.txt file such that only oracle user has read permission and no other permissions set.[oracle@rhel6m1 exdata]$ chmod 400 permission.txt [oracle@rhel6m1 exdata]$ ls -l permission.txt -r--------. 1 oracle oinstall 272 Oct 9 15:38 permission.txt9. Create the external table using this csv file
create table file_perms (
permission varchar2(12),
owner varchar2(15),
groups varchar2(15),
file_name varchar2(40))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY exec_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
) LOCATION ('permission.txt'))
PARALLEL 5
REJECT LIMIT UNLIMITED;10.Case 4 CSV file having only read permission for Oracle userTest 4.1 Run select query with a local connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa SQL> select * from file_perms; PERMISSION OWNER GROUPS FILE_NAME ------------ --------------- --------------- ------------------------- -rw-r--r--. oracle asmadmin DF_27539.log -rw-r--r--. oracle asmadmin DF_27545.log -rw-r--r--. oracle asmadmin FILE_PERMS_27818.log -rw-r--r--. oracle asmadmin FILE_PERMS_27842.log -r-xr-----. oracle dba run_df.sh -rwxr-xr-x. root root status.sh -rwxr-xr-x. root root t.shLocal connection returns rows.
Test 4.2 Run select with a remote connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from file_perms; PERMISSION OWNER GROUPS FILE_NAME ------------ --------------- --------------- ------------------------- -rw-r--r--. oracle asmadmin DF_27539.log -rw-r--r--. oracle asmadmin DF_27545.log -rw-r--r--. oracle asmadmin FILE_PERMS_27818.log -rw-r--r--. oracle asmadmin FILE_PERMS_27842.log -r-xr-----. oracle dba run_df.sh -rwxr-xr-x. root root status.sh -rwxr-xr-x. root root t.shRemote connection returns rows as well and there's no difference to the output due to the fact oinstall group not having any permission the csv file.
11. Case 5 Change the permission on the directory containing the files such that only oracle has full set of permission and no other permissions. Only the directory's permission are changed permissions of the file inside the directory are unchanged.
[oracle@rhel6m1 local]$ chmod 700 exdata drwx------. 2 oracle oinstall 4096 Oct 9 15:48 exdataTest 5.1 Run select query with a local connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa SQL> select * from file_perms; PERMISSION OWNER GROUPS FILE_NAME ------------ --------------- --------------- ------------------------- -rw-r--r--. oracle asmadmin DF_27539.log -rw-r--r--. oracle asmadmin DF_27545.log -rw-r--r--. oracle asmadmin FILE_PERMS_27818.log -rw-r--r--. oracle asmadmin FILE_PERMS_27842.log -r-xr-----. oracle dba run_df.sh -rwxr-xr-x. root root status.sh -rwxr-xr-x. root root t.shLocal connection returns rows as before.
Test 5.2 Run select with a remote connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from file_perms; select * from file_perms * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file permission.txt in EXEC_DIR not foundRemote connection fails to return any rows. The error says file permission.txt is not found in the directory which is not true. Again same table, same query two different outputs based on whether connection being local or remote. The same behavior could be seen for running the table that uses shell script file as well.
Test 5.3 Run select query with a local connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa SQL> select * from df; FSNAME BLOCKS USED AVAIL CAPACITY MOUNT ---------- ---------- ---------- ---------- ---------- ---------- /dev/sda3 37054144 32706324 2465564 93% / tmpfs 1961580 228652 1732928 12% /dev/shm /dev/sda1 99150 27725 66305 30% /bootRunning select with a remote connection
Test 5.4
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from df; select * from df * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file run_df.sh in /usr/local/exdata not found12. Case 6 Oracle user having full permission and group having execute permission on the directory.
Change the permission of the directory containing the csv file as shown below
[oracle@rhel6m1 local]$ chmod 710 exdata drwx--x---. 2 oracle oinstall 4096 Oct 9 15:59 exdataTest 6.1 Run select query with a local connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa SQL> select * from file_perms; PERMISSION OWNER GROUPS FILE_NAME ------------ --------------- --------------- ------------------------- -rw-r--r--. oracle asmadmin DF_27539.log -rw-r--r--. oracle asmadmin DF_27545.log -rw-r--r--. oracle asmadmin FILE_PERMS_27818.log -rw-r--r--. oracle asmadmin FILE_PERMS_27842.log -r-xr-----. oracle dba run_df.sh -rwxr-xr-x. root root status.sh -rwxr-xr-x. root root t.shLocal connection returns rows.
Test 6.2 Run select with a remote connection
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from file_perms; PERMISSION OWNER GROUPS FILE_NAME ------------ --------------- --------------- ------------------------- -rw-r--r--. oracle asmadmin DF_27539.log -rw-r--r--. oracle asmadmin DF_27545.log -rw-r--r--. oracle asmadmin FILE_PERMS_27818.log -rw-r--r--. oracle asmadmin FILE_PERMS_27842.log -r-xr-----. oracle dba run_df.sh -rwxr-xr-x. root root status.sh -rwxr-xr-x. root root t.shRemote connection returns rows as well. Unlike before output is same for both remote and local connection. This permission (710) also works for the external table based on the shell script
[oracle@rhel6m1 exdata]$ sqlplus asanga/asa@std11g21 SQL> select * from df; FSNAME BLOCKS USED AVAIL CAPACITY MOUNT ---------- ---------- ---------- ---------- ---------- ---------- /dev/sda3 37054144 32697924 2473964 93% / tmpfs 1961580 228652 1732928 12% /dev/shm /dev/sda1 99150 27725 66305 30% /bootIn summary when external tables are created in a role separated environment group permissions must be set for files/directories used for creating the external tables. If not the output may vary depending on whether the connection is a local connection or a remote connection.
Tested on 11.2.0.3, 11.2.0.4 and 12.1.0.1 (non-CDB)
