Flex table view grant error

Answers

• mosheg Vertica Employee Administrator

  October 2020

  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)
  

  0
• SergeB - Select Field - Employee

  October 2020

  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;

  0
• levkiss Community Edition User

  October 2020 edited October 2020

  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.

  0
• mosheg Vertica Employee Administrator

  October 2020

  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)
  

  0