Hello,
The other day I encountered a weird situation regarding a view which was not visible to other users even though the grant option was executed. This happened in Vertica 7.1.1:
1. The view was created: CREAT VIEW Schema.ExampleView AS complex query...;
2. GRANT SELECT on Schema.ExampleView TO joe;
3. User Joe was notified that the view was created.
4. User Joe could no see the view and when he tried to query the view (select * from Schema.ExampleView ) he got the error "Permission denied for relation ExampleView "
5. I checked the Vertica System tables for the permissions over that view and the result said that user joe had select permissions over the view.
I tried dropping the view an recreating it, assigning the permissions to user joe once again but the issue continued. I ran the query by itself and it runs fine without any errors. The weird thing is that when I created a view with a simpler query and gave permission to user joe, that view he could see. The problem was that he could not see the other view with the more complex query.
Could this be linked to the type of query used in the view? or is there a bug in Vertica regarding views?
Thanks!!!
View not visible to other users
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: View not visible to other users
I believe they need permissions to all the involved underlying view tables.
Checkout vertica.tips for more Vertica resources.
Re: View not visible to other users
Thank you for the quick reply!
We checked the permissions for user "joe" on all the referenced tables, just in case, and he had permissions on them. User joe claimed that when he ran the query by itself that it ran fine, without any issues. When the query was implemented into a view the user could not see the view even though the appropriate grant had been given. Does the user really need access to all the referenced tables? The vertica documentation states the following for the SELECT grant parameter: "Grants a user or role SELECT operations to a view, and any resources referenced within it."
http://my.vertica.com/docs/7.1.x/HTML/i ... NTView.htm
Doesn't this mean no further grants over tables should be needed or are they still required?
We checked the permissions for user "joe" on all the referenced tables, just in case, and he had permissions on them. User joe claimed that when he ran the query by itself that it ran fine, without any issues. When the query was implemented into a view the user could not see the view even though the appropriate grant had been given. Does the user really need access to all the referenced tables? The vertica documentation states the following for the SELECT grant parameter: "Grants a user or role SELECT operations to a view, and any resources referenced within it."
http://my.vertica.com/docs/7.1.x/HTML/i ... NTView.htm
Doesn't this mean no further grants over tables should be needed or are they still required?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: View not visible to other users
Maybe you should just fire Joe
You should not need to grant privileges on the underlying tables...
Example:
Questions:
1. Which user created the view?
2. Which user granted the SELECT privilege to Joe?
3. What do you mean by complex query?
You should not need to grant privileges on the underlying tables...
Example:
Code: Select all
dbadmin=> create user jim;
CREATE USER
dbadmin=> create schema jim;
CREATE SCHEMA
dbadmin=> create table jim.jim (c int);
CREATE TABLE
dbadmin=> insert into jim.jim values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> create view jim.jim_vw as select * from jim.jim;
CREATE VIEW
dbadmin=> grant select on jim.jim_vw to jim;
WARNING 5682: USAGE privilege on schema "jim" also needs to be granted to "jim"
GRANT PRIVILEGE
dbadmin=> grant usage on schema jim to jim;
GRANT PRIVILEGE
dbadmin=> \c dbadmin jim
You are now connected to database "dbadmin" as user "jim".
dbadmin=> select * from jim.jim_vw;
c
---
1
(1 row)
dbadmin=> select * from jim.jim;
ERROR 4367: Permission denied for relation jim
1. Which user created the view?
2. Which user granted the SELECT privilege to Joe?
3. What do you mean by complex query?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: View not visible to other users
We should fire Joe
1. Which user created the view?
We used a "superuser" to create the view. We have created various views for user joe in the past without issues.
2. Which user granted the SELECT privilege to Joe?
Our superuser, the same as the one who created the view
3. What do you mean by complex query?
Sorry by complex I mean a query with many subqueries, cases, and left joins.
I think is worthwhile to mention that the view had existed before but with a different query, and it worked fine, user joe could see it. The problem rose when joe modified the query to adjust it in order for it to meet different conditions, when the view was updated with the new query that is when the problem surged. I created a temp view with the older query to test if joe could see it and he could, but when the view was created with the new query joe could not see it.
Could this be cause by a bug or something? it is very strange
1. Which user created the view?
We used a "superuser" to create the view. We have created various views for user joe in the past without issues.
2. Which user granted the SELECT privilege to Joe?
Our superuser, the same as the one who created the view
3. What do you mean by complex query?
Sorry by complex I mean a query with many subqueries, cases, and left joins.
I think is worthwhile to mention that the view had existed before but with a different query, and it worked fine, user joe could see it. The problem rose when joe modified the query to adjust it in order for it to meet different conditions, when the view was updated with the new query that is when the problem surged. I created a temp view with the older query to test if joe could see it and he could, but when the view was created with the new query joe could not see it.
Could this be cause by a bug or something? it is very strange
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: View not visible to other users
How are you modifying the query for the view? Don't forget that whether using the DROP/CREATE or CREATE OR REPLACE options, you need to re-grant the SELECT privilege to Joe.
If that is not the issue, can you post examples? We don't need to see data, so you can log in as Joe and run a SELECT COUNT(*) against the view that is not working. Also, have the user Joe run the query
If that is not the issue, can you post examples? We don't need to see data, so you can log in as Joe and run a SELECT COUNT(*) against the view that is not working. Also, have the user Joe run the query
Code: Select all
SELECT object_schema, object_name, object_type FROM grants WHERE grantee ilike '%joe%';
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: View not visible to other users
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.