External Tables and Privileges

A typical use case in a Data Warehouse is that flat files are loaded into the Staging Area via external tables. The ETL process reads the contents of the file with a SELECT statement on an external table and writes it to the corresponding stage table. As long as the ETL tool connects directly to the schema where the external table is created, this is simple. But how about different schemas? 

Let’s assume we have the following configuration:

  • An external table EXT_CUSTOMER and a stage table STG_CUSTOMER are created in the schema DWH_STAGE.
  • For more flexibility, a view V_SRC_CUSTOMER is created on top of the external table. This allows to switch easily to another source without changing the ETL process. For example, the view can be changed to read test data from a mock-up table instead of the external table.
  • The ETL tool connects to a user DWH_ETL which needs the required privileges to read the data from the source view V_SRC_CUSTOMER and write it to the stage table STG_CUSTOMER.
What are the required privileges to read data from a view? The Oracle Database Security Guide (Oracle 11.2 / Oracle 12.1) gives the answer in the section Increasing Table Security with Views:
 

To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access. This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view’s definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer’s rights have been revoked from the view’s underlying objects.

Test Case Example in DWH_STAGE

For our example, this means: The user DWH_ETL needs SELECT privileges on the view V_SRC_CUSTOMER, but not on the underlying table. This is the common situation as we know it from regular tables and views. Let’s implement this for our use case. First, we create a logical directory and the external table:
 
CREATE OR REPLACE DIRECTORY dwh_stage_dir AS '/app/dwh/stage';
CREATE TABLE ext_customer
( cust_nr VARCHAR2(4)
, last_name VARCHAR2(25)
, first_name VARCHAR2(20)
, city VARCHAR2(30)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY dwh_stage_dir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE dwh_stage_dir:'customer.bad'
LOGFILE dwh_stage_dir:'customer.log'
FIELDS TERMINATED BY "," LDRTRIM
)
LOCATION ('customer.csv')
)
REJECT LIMIT UNLIMITED;

Now, we create the source view on the external table and give the SELECT privilege to the user DWH_ETL:

CREATE OR REPLACE VIEW v_src_customer AS
SELECT cust_nr
, last_name
, first_name
, city
FROM ext_customer;

GRANT SELECT ON v_src_customer TO dwh_etl;

Missing Privileges for DWH_ETL

The user DWH_ETL should now be able to read data from the view. Really?

SELECT COUNT(*) FROM dwh_stage.v_src_customer
*
ERROR at line 1:
ORA-06564: object DWH_STAGE_DIR does not exist

Oops, it seems that additional privileges on the directory are required not only for the table owner, but also for the user that selects the view. To avoid further access problems with the directory, we must define read and write access to the DWH_ETL user. The write privileges are required to write the badfile and logfile.

GRANT READ, WRITE ON DIRECTORY dwh_stage_dir TO dwh_etl;

Surprisingly, even with these additional privileges on the directory, the user DWH_ETL is not yet able to read the view:

SELECT COUNT(*) FROM dwh_stage.v_src_customer
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-04043: object "DWH_STAGE"."EXT_CUSTOMER" does not exist

The underlying table is not visible, and the only way to fix this issue is to grant SELECT privileges on the table directly:

GRANT SELECT ON ext_customer TO dwh_etl;

Finally, the user DWH_ETL is able to read data from the external table through the source view:

SELECT COUNT(*) FROM dwh_stage.v_src_customer;

COUNT(*)
----------
6727

Conclusion

Although Oracle defines that a user must only have privileges on the view itself, not its underlying objects, this seems not to work for external tables. I tested this use case with Oracle 11.2.0.3 and Oracle 12.1.0.1. In both versions, the behavior of access right for views on external tables is different than for regular tables. I did not yet find an explanation for that.

Addendum

(04.07.2014) In the document VIEWS OVER EXTERNAL TABLES (Doc ID 1530608.1) on “My Oracle Support”, I found the explanation (after opening a Service Request):

When a view is created over an external table, if select on the view is granted to another user it is not sufficient privilege to be able to select from the view, as with standard views, even if the user has read privilege on the associated directory. It is also necessary for the user to be granted select on the external table directly. This is not documented.

The problem arises since the access driver for the external table needs to do a DESCRIBE of the external table internally for which the user has no privileges.

There’s currently no fix. Development are working on a fix in bug 8755195 to be available in a future release of Oracle Database. The workaround is to grant select on the external table to the user also.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s