Wednesday, October 9, 2013

Role Separation and External Tables

Success of a select query on a external table that is created in a system with role separation whether it's single instance or RAC will depend on the connection (local or remote) and the permissions of the directories/files used for the external table. This seem to be odd behavior as output for a query shouldn't depend on the nature of the connection, if it fails or return data for a local connection same should happen for a remote connection. If one set of permission return results for a locally connected session then same permission should also return data for a remote connection.
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 exdata
Directory 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 -Pl
3. 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  NO
4. 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.sh
Test 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%        /boot
Test 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 denied
As 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.sh
Test 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 denied
This 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%        /boot
Running 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.sh
Test 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%        /boot
Test 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%        /boot
This 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.sh
Copy 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.txt
9. 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 user
Test 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.sh
Local 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.sh
Remote 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 exdata
Test 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.sh
Local 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 found
Remote 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%        /boot
Running 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 found
12. 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 exdata
Test 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.sh
Local 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.sh
Remote 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%        /boot
In 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)