Flex table view grant error

levkiss

Hello
I got a very strange error. The point is that I need to create a flex table as administrator and then use the compute_flextable_keys_and_build_view () function.

This only works well when I do the procedure as an administrator. When I try to do this from the user, I get a SQL Error [3989] [42501]: [Vertica] [VJDBC] (3989) ERROR: Must be owner of relation FLEX_DATA_view.

The problem is that I give all the rights to the schema to the user, but they do not appear in the view.

You can reproduce this error the following way:
dbadmin:
1. CREATE SCHEMA MY_SC DEFAULT INCLUDE SCHEMA PRIVILEGES;
2. create user test identified by 'passwd';
3. GRANT CREATE, SELECT, INSERT, UPDATE, TRUNCATE, DELETE, REFERENCES, USAGE ON SCHEMA MY_SC TO test;
4. create flex table MY_SC.FLEX_DATA();

user test:
1. select compute_flextable_keys_and_build_view('MY_SC.FLEX_DATA');
And here i get error((

How can you solve this?

mosheg

As mentioned in the error message, user test should be the owner for the relation view.
To create a view under test user ownership, try this:

cat view_grant.sql
\c - dbadmin
-- You are now connected as user "dbadmin".
DROP SCHEMA MY_SC CASCADE;
-- DROP SCHEMA
DROP USER test01 CASCADE;
-- DROP USER
CREATE SCHEMA MY_SC DEFAULT INCLUDE SCHEMA PRIVILEGES;
-- CREATE SCHEMA
CREATE USER test01;
-- CREATE USER
CREATE FLEX TABLE MY_SC.FLEX_DATA();
-- CREATE TABLE
GRANT ALL ON SCHEMA MY_SC,PUBLIC TO test01;
-- GRANT PRIVILEGE
GRANT ALL PRIVILEGES ON MY_SC.FLEX_DATA TO test01;
-- GRANT PRIVILEGE
copy MY_SC.FLEX_DATA from STDIN parser FDELIMITEDPARSER() delimiter ',' abort on error;
f1,f2,f3,f4,f5
5,6,7,lalala,8
50,60,70,papapa,80
\.

\c - test01
-- You are now connected as user "test01".
SELECT COMPUTE_FLEXTABLE_KEYS('MY_SC.FLEX_DATA');
--               COMPUTE_FLEXTABLE_KEYS
-- --------------------------------------------------
--  Please see MY_SC.FLEX_DATA_keys for updated keys
-- (1 row)

SELECT BUILD_FLEXTABLE_VIEW('MY_SC.FLEX_DATA','MY_SC.my_view','MY_SC.FLEX_DATA_keys');
--             BUILD_FLEXTABLE_VIEW
-- ----------------------------------------------
--  The view MY_SC.my_view is ready for querying
-- (1 row)

SELECT * FROM MY_SC.my_view;
--  f1 | f2 | f3 |   f4   | f5
-- ----+----+----+--------+----
--   5 |  6 |  7 | lalala |  8
--  50 | 60 | 70 | papapa | 80
-- (2 rows)

SergeB

What version of Vertica are you using in your test?
Could you also check the following after creating your flex table:
select name, inheritPrivileges from vs_tables where name like 'flex_data%' order by name;
and
select table_name, inherit_privileges from views where table_name like 'flex_data%' order by table_name;

levkiss

Thanks for your immediate answer!
I'm using vertica container for docker. The version of vertica is 10.0.1-0.

When i perform this query:
select name, inheritPrivileges from vs_tables where name like 'flex_data%' order by name;
and
select table_name, inherit_privileges from views where table_name like 'flex_data%' order by table_name;
I get a table consisting of 2 rows FLEX_DATA and 2 rows FLEX_DATA_KEYS, but not FLEX_DATA_VIEW.

This query from below works well, but i need to create a view named 'MY_SC.FLEX_DATA_view' instead of 'MY_SC.my_view'
SELECT BUILD_FLEXTABLE_VIEW('MY_SC.FLEX_DATA','MY_SC.my_view','MY_SC.FLEX_DATA_keys');

I need to create a table FLEX_DATA as a superuser and then alow some other users perform the following query without getting ERROR:
select compute_flextable_keys_and_build_view('MY_SCFLEX_DATA');

Why my flex table's view 'MY_SC.my_view' (which is created automatically when the FLEX_DATA table is created) doesn't inherit MY_SC grants like other tables? I dont even see this view when i enter as test user.

mosheg

Since only the owner of FLEX_DATA_view can run the BUILD_FLEXTABLE_VIEW,
If dbadmin will do it first as shown below, given that other users have the right GRANTs, they will be able to use that view later on.

\c - dbadmin
-- You are now connected as user "dbadmin".
CREATE SCHEMA MY_SC DEFAULT INCLUDE SCHEMA PRIVILEGES;
CREATE USER user01;
CREATE FLEX TABLE MY_SC.FLEX_DATA();

GRANT ALL ON SCHEMA MY_SC,PUBLIC TO user01;
GRANT ALL PRIVILEGES ON MY_SC.FLEX_DATA TO user01;
GRANT ALL PRIVILEGES ON MY_SC.FLEX_DATA_view TO user01;

COPY MY_SC.FLEX_DATA from STDIN parser FDELIMITEDPARSER() delimiter ',' abort on error;
f1,f2
1,dbadmin data
\.

SELECT COMPUTE_FLEXTABLE_KEYS('MY_SC.FLEX_DATA');
SELECT BUILD_FLEXTABLE_VIEW('MY_SC.FLEX_DATA');

\c - user01
-- You are now connected as user "user01".
COPY MY_SC.FLEX_DATA from STDIN parser FDELIMITEDPARSER() delimiter ',' abort on error;
f1,f2
2,user01 data
\.

SELECT * FROM MY_SC.FLEX_DATA_view;
  f1 |      f2
 ----+--------------
   1 | dbadmin data
   2 | user01 data
 (2 rows)