Views vs Table Access

Moderator: NorbertKrupa

Post Reply
jenellis
Newbie
Newbie
Posts: 3
Joined: Fri Aug 29, 2014 8:20 pm

Views vs Table Access

Post by jenellis » Tue Sep 02, 2014 6:02 pm

Hi,

I have a requirement to give read access to database users. Does anyone have an opinion if it's best practice to create a view for every table and grant the select privilege on them to users, or simply give read only access directly on the tables?

Thank you.

-J

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

Re: Views vs Table Access

Post by scutter » Tue Sep 02, 2014 6:37 pm

I would grant the access directly on the tables, but grant the access to roles, and then grant the roles to the users and set the roles as default roles for the users as well.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

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

Re: Views vs Table Access

Post by NorbertKrupa » Tue Sep 02, 2014 6:57 pm

Also want to add that views in Vertica require privileges on the tables involved in the view, thus it just makes more sense to grant directly to the tables.
Checkout vertica.tips for more Vertica resources.

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

Re: Views vs Table Access

Post by JimKnicely » Wed Sep 03, 2014 12:51 pm

norbertk, I am not sure that this statement is true:
views in Vertica require privileges on the tables involved in the view
Example...

Setup:

Code: Select all

dbadmin=> create schema jim;
CREATE SCHEMA

dbadmin=> create user jim;
CREATE USER

dbadmin=> grant all on schema jim to jim;
GRANT PRIVILEGE

dbadmin=> create user jane;
CREATE USER

dbadmin=> grant usage on schema jim to jane;
GRANT PRIVILEGE

dbadmin=> \c dbadmin jim
Password:
You are now connected to database "dbadmin" as user "jim".

jim=> create table jim.test (t1 int);
CREATE TABLE

jim=> insert into jim.test values (1);
 OUTPUT
--------
      1
(1 row)

jim=> commit;
COMMIT

jim=> create view jim.test_vw as select * from jim.test;
CREATE VIEW

jim=> grant select on jim.test_vw to jane;
GRANT PRIVILEGE
Now when I log in as the user jane, I can SELECT from the view, but not the table:

Code: Select all

jim=> \c dbadmin jane
Password:
You are now connected to database "dbadmin" as user "jane".

jane=> select * from jim.test_vw;
 t1
----
  1
(1 row)

jane=> select * from jim.test;
ERROR 4367:  Permission denied for relation test
Jim Knicely

Image

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

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

Re: Views vs Table Access

Post by NorbertKrupa » Wed Sep 03, 2014 2:46 pm

Hmm, I need to test that out using roles though.
Checkout vertica.tips for more Vertica resources.

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

Re: Views vs Table Access

Post by JimKnicely » Wed Sep 03, 2014 6:35 pm

It does work via a role too :)

Code: Select all

dbadmin=> create schema jim;
CREATE SCHEMA

dbadmin=> create table jim.test (t1 int);
CREATE TABLE

dbadmin=> insert into jim.test values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> create view jim.test_vw as select * from jim.test;
CREATE VIEW

dbadmin=> create role jim_role;
CREATE ROLE

dbadmin=> create user jane;
CREATE USER

dbadmin=> grant usage on schema jim to jim_role;
GRANT PRIVILEGE

dbadmin=> grant select on jim.test_vw to jim_role;
GRANT PRIVILEGE

dbadmin=> grant jim_role to jane;
GRANT ROLE

dbadmin=> alter user jane default role jim_role;
ALTER USER

dbadmin=> \c dbadmin jane
You are now connected to database "dbadmin" as user "jane".

jane=> select * from jim.test;
ERROR 4367:  Permission denied for relation test

jane=> select * from jim.test_vw;
 t1
----
  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.

Post Reply

Return to “Vertica Security”