View not visible to other users

Moderator: NorbertKrupa

Manolo
Newbie
Newbie
Posts: 3
Joined: Wed Nov 25, 2015 7:46 pm

View not visible to other users

Post by Manolo » Mon Feb 08, 2016 11:36 pm

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!!!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: View not visible to other users

Post by NorbertKrupa » Tue Feb 09, 2016 5:33 pm

I believe they need permissions to all the involved underlying view tables.
Checkout vertica.tips for more Vertica resources.

Manolo
Newbie
Newbie
Posts: 3
Joined: Wed Nov 25, 2015 7:46 pm

Re: View not visible to other users

Post by Manolo » Tue Feb 09, 2016 7:23 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: View not visible to other users

Post by JimKnicely » Tue Feb 09, 2016 10:05 pm

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?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Manolo
Newbie
Newbie
Posts: 3
Joined: Wed Nov 25, 2015 7:46 pm

Re: View not visible to other users

Post by Manolo » Tue Feb 09, 2016 10:46 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: View not visible to other users

Post by JimKnicely » Wed Feb 10, 2016 3:04 am

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%';
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: View not visible to other users

Post by JimKnicely » Wed Feb 10, 2016 3:11 am

Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica”