Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Flex table view grant error

edited October 2020 in General Discussion

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?

Answers

  • moshegmosheg Administrator

    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)
    
  • SergeBSergeB Employee

    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;

  • 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.

  • moshegmosheg Administrator

    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)
    

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.