Flex table view grant error
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?
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
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:
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;
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.
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.