We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


VIEW with UNION and GRANT SELECT = error privileges — Vertica Forum

VIEW with UNION and GRANT SELECT = error privileges

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.

 

Comments

  • Resolved:

    GRANT USAGE ON SCHEMA raw TO roleUser;

     

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file