GRANT ANY OBJECT PRIVILEGE may fail with views

Detail name	Value
Changelog Number	8681
Type	Bug
Status	Resolved
Affected Versions	Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 6.2.13, Exasol 7.0.5
Resolution Date	2020-12-03

Background

Users with the privilege GRANT ANY OBJECT PRIVILEGE should be able to GRANT SELECT ON any objects without requiring SELECT privileges on these objects.

In version 7.0, USAGE is also required for the user to grant SELECT privileges on all objects.

Description

In contrast to the documentation GRANT ANY OBJECT PRIVILEGE are not sufficient for GRANT SELECT ON statements on views.

Preparation

-- Executed as SYS
create user user1 identified by exasol;
create user user2 identified by exasol;
create user user3 identified by exasol;

create schema schema1;
alter schema schema1 change owner user1;
create or replace table schema1.t1 (c1 int);
create or replace view schema1.view1 as select * from schema1.t1;

grant grant any object privilege to user2;
grant impersonation on sys to user2;

Example

impersonate user2;
-- run as user 2
grant select on schema1.t1 to user3; -- works as expected
grant select on schema1.view1 to user3; -- doesn't work due to "insufficient privileges for accessing view VIEW1"

Workaround

You can grant additional SELECT privileges to the user that will enable the user to grant SELECT on the view to other users. In addition, you can grant SELECT ANY TABLE to the user who is performing the GRANT statements.

impersonate sys;
grant select on schema1.view1 to user2;

impersonate user2;

grant select on schema1.t1 to user3; -- works as expected
grant select on schema1.view1 to user3; -- also works

Fix

If the user has the GRANT ANY OBJECT PRIVILEGE privilege, no additional privileges are required to grant SELECT on views to other users.