Page 1 of 2

View not visible to other users

Posted: Mon Feb 08, 2016 11:36 pm
by Manolo
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!!!

Re: View not visible to other users

Posted: Tue Feb 09, 2016 5:33 pm
by NorbertKrupa
I believe they need permissions to all the involved underlying view tables.

Re: View not visible to other users

Posted: Tue Feb 09, 2016 7:23 pm
by Manolo
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? :?

Re: View not visible to other users

Posted: Tue Feb 09, 2016 10:05 pm
by JimKnicely
Maybe you should just fire Joe :lol:

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
Questions:

1. Which user created the view?
2. Which user granted the SELECT privilege to Joe?
3. What do you mean by complex query?

Re: View not visible to other users

Posted: Tue Feb 09, 2016 10:46 pm
by Manolo
We should fire Joe :lol:

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 :oops: 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. :shock:

Could this be cause by a bug or something? it is very strange

Re: View not visible to other users

Posted: Wed Feb 10, 2016 3:04 am
by JimKnicely
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

Code: Select all

SELECT object_schema, object_name, object_type FROM grants WHERE grantee ilike '%joe%';

Re: View not visible to other users

Posted: Wed Feb 10, 2016 3:11 am
by JimKnicely