Objects invisible to users when NOT created by dbadmin

Moderator: NorbertKrupa

Post Reply
dtseiler
Newbie
Newbie
Posts: 1
Joined: Mon Jul 23, 2018 10:17 pm

Objects invisible to users when NOT created by dbadmin

Post by dtseiler » Mon Jul 23, 2018 10:23 pm

I have inherited a database where all objects are owned by dbadmin. I have roles set up to grant privileges out to users, normally this works fine.

However in order to spare myself some work, I wanted to allow developers to create/alter/drop objects they need on our dev instance, through a "developer" role. In this case he created a view. This seemed to work fine, the view was there, owned by the developer's account. He granted SELECT access to the "it_user" role. I could query it from the dbadmin account.

However, none other users could use it, getting "permission denied" errors. I tripled checked that the roles has SELECT privs and the roles were granted to the users as default, but still nothing.

On a whim I recreated the view as dbadmin with the same script that my developer used for his account. Everything works fine now.

Why can't users see the view created by a developer account?

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

Re: Objects invisible to users when NOT created by dbadmin

Post by JimKnicely » Sun Jul 29, 2018 2:45 pm

Check out the "Privileges Required for Common Database Operations" doc. In particular, the "Object Visibility" section:

Here:
https://my.vertica.com/docs/9.1.x/HTML/ ... ations.htm

Example:

dbadmin=> CREATE SCHEMA dev DEFAULT INCLUDE PRIVILEGES;
CREATE SCHEMA

dbadmin=> CREATE USER dev1;
CREATE USER

dbadmin=> CREATE USER dev2;
CREATE USER

dbadmin=> GRANT USAGE, CREATE, SELECT, INSERT, UPDATE, DELETE ON SCHEMA dev TO it_role;
GRANT PRIVILEGE

dbadmin=> CREATE ROLE it_user;
CREATE ROLE

dbadmin=> GRANT USAGE, CREATE, SELECT, INSERT, UPDATE, DELETE ON SCHEMA dev TO it_user;
GRANT PRIVILEGE

dbadmin=> GRANT it_user TO dev1, dev2;
GRANT ROLE

dbadmin=> ALTER USER dev1 DEFAULT ROLE it_user;
ALTER USER

dbadmin=> ALTER USER dev2 DEFAULT ROLE it_user;
ALTER USER

dbadmin=> \c - dev1
You are now connected as user "dev1".

dbadmin=> CREATE TABLE dev.t1 (c INT);
WARNING 6978: Table "t1" will include privileges from schema "dev"
CREATE TABLE

dbadmin=> INSERT INTO dev.t1 SELECT 1;
OUTPUT
--------
1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> CREATE VIEW dev.t1_vw AS SELECT c FROM dev.t1;
WARNING 7070: View "t1_vw" will include privileges from schema "dev"
CREATE VIEW

dbadmin=> \c - dev2
You are now connected as user "dev2".

dbadmin=> SELECT * FROM dev.t1_vw;
c
---
1
(1 row)
Jim Knicely

Image

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

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Objects invisible to users when NOT created by dbadmin

Post by scutter » Thu Aug 02, 2018 7:14 pm

Jim - I've seen this too. It's an issue for non-dbadmin users - to reproduce it you'd have to use a different account and create the view as a non-dbadmin user. You can workaround it by changing the owner of the view to be dbadmin but that's not always ideal. I haven't chased the issue any further to fully understand what's going on.

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “Vertica Security”