Giving grant on a view also asks for a grant on a directory

user_6443676 0
Viewed 57

I want to create an external table that collects some data from server and returns it in a more handy way as a table. Also I want to cover this table with a view and give SELECT privilege to another user. I create a preset under some privileged user

PS C:\> sqlplus privileged/user@example

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 11 15:33:17 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> grant connect to u1;

Grant succeeded.

SQL> grant connect to u2;

Grant succeeded.

SQL>
SQL> CREATE TABLE u1.status_all (
  2      text    VARCHAR2(1000)
  3  )
  4  ORGANIZATION EXTERNAL (
  5      TYPE    ORACLE_LOADER
  6      DEFAULT DIRECTORY GG_SCRIPTS_DIR
  7      ACCESS PARAMETERS (
  8          records delimited by newline
  9          preprocessor 'status_all.sh'
 10          nobadfile
 11          nodiscardfile
 12          nologfile
 13          disable_directory_link_check
 14          fields terminated by eof
 15          missing field values are null
 16      )
 17      LOCATION (
 18          GG_SCRIPTS_DIR:'status_all.sh'
 19      )
 20  )
 21  REJECT LIMIT UNLIMITED;

Table created.

SQL>
SQL> grant read,execute on directory GG_SCRIPTS_DIR to u1 with grant option;

Grant succeeded.

SQL>

Okay, what about results?

PS C:\> sqlplus u1/u1@example

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 11 15:33:52 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select * from status_all where rownum <= 5;

TEXT
--------------------------------------------------------------------------------

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as UTF-8.

SQL>

Cool, it works! Let's create a view under a privileged user:

SQL> create or replace view u1.vstatus_all as
  2  select regexp_substr(t.text, '[[:alnum:]]{1,}' , 1, 3, 'i') proc_name,
  3         regexp_substr(t.text, '^[[:alnum:]]{1,}', 1, 1, 'i') proc_type,
  4         regexp_substr(t.text, '[[:alnum:]]{1,}' , 1, 2, 'i') proc_status,
  5         regexp_substr(t.text, '[[:alnum:]:]{1,}', 1, 4, 'i') lag_at_chkpt,
  6         regexp_substr(t.text, '[[:alnum:]:]{1,}', 1, 5, 'i') time_since_chkpt
  7  from   u1.status_all t
  8  where  regexp_like(t.text, '(MANAGER|EXTRACT|REPLICAT)');

View created.

How does it look like from u1 point of view?

SQL> select * from u1.vstatus_all;

PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------

MANAGER
RUNNING

PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------




PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
EADS1
EXTRACT
RUNNING

PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
00:00:00
00:00:00


PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
PADS1BB1
EXTRACT
RUNNING

PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
00:00:00
00:00:08


PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
RABD1DS1
REPLICAT
RUNNING

PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
00:00:00
00:00:04


PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
RZ011DS1
REPLICAT
RUNNING

PROC_NAME
--------------------------------------------------------------------------------
PROC_TYPE
--------------------------------------------------------------------------------
PROC_STATUS
--------------------------------------------------------------------------------
LAG_AT_CHKPT
--------------------------------------------------------------------------------
TIME_SINCE_CHKPT
--------------------------------------------------------------------------------
00:00:00
00:00:00

Yeah, it works! It's time to give all the rights to u2 (under privileged user):

SQL> grant select on u1.vstatus_all to u2;

Grant succeeded.

Let's check it:

PS C:\> sqlplus u2/u2@example

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 11 15:41:13 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select * from u1.vstatus_all;
select * from u1.vstatus_all
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-04043: object "U1"."STATUS_ALL" does not exist


SQL>

How come it requires STATUS_ALL access? I created a view to hide this table from u2. I've managed to get it to work only after giving grants on directory and underlying table:

SQL> grant select on u1.status_all to u2;

Grant succeeded.

SQL> grant read,execute on directory GG_SCRIPTS_DIR to u2;

Grant succeeded.

SQL>

With such grants u2 can easily create required table without any view grants. Does u1 or u2 lack some other grants?

oracle
1 Answers

After some googling I found this article that states exactly the same case. Also it leads to Doc ID 1530608.1 on Metalink that explains such behaviour:

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.

user_64436760
Related