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 9 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 Employee

    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 9

    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 Employee

    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.