VIEW with UNION and GRANT SELECT = error privileges

anki1

Vertica 8:

When I try to use VIEW with UNION on user who have permissions only for SELECT FROM VIEW, I have an error with privilegies:

 

 

-- Run from dba:

-- 1. Create schema RAW for real tables

CREATE SCHEMA raw;
CREATE TABLE raw.Table1 (x int);

-- 2. Create schema DM for views

CREATE SCHEMA dm;

CREATE OR REPLACE VIEW dm.View1 AS
(
	SELECT x FROM raw.Table1 WHERE x=1
	union all
	SELECT x FROM raw.Table1 WHERE x=2
);

-- 3. Create role User and one User

CREATE ROLE roleUser;

CREATE USER SomeUser IDENTIFIED BY '123456';

-- 4. Grant user: use only DM schema with views

ALTER USER SomeUser DEFAULT ROLE roleUser;
GRANT roleUser TO SomeUser;
GRANT USAGE ON SCHEMA dm TO roleUser;
GRANT SELECT ON dm.View1 TO roleUser;

-- 5. Reconnect to SomeUser and select:

SELECT * FROM dm.View1;

And I've got:

 

An error occurred when executing the SQL command:
SELECT * FROM dm.View1

[Vertica][VJDBC](3580) ERROR: Insufficient privilege: USAGE on SCHEMA 'raw' not granted for current user [SQL State=42501, DB Errorcode=3580]
1 statement failed.

Execution time: 0.37s

 

If I use VIEW without UNION everything is fine.

 

Please help to fix this problem.

 

anki1

Resolved:

GRANT USAGE ON SCHEMA raw TO roleUser;

 

But it's not obvious solution. JOIN in VIEW works perfect without GRANT USAGE raw.