Page 1 of 1

Views vs Table Access

Posted: Tue Sep 02, 2014 6:02 pm
by jenellis
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

Re: Views vs Table Access

Posted: Tue Sep 02, 2014 6:37 pm
by scutter
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

Re: Views vs Table Access

Posted: Tue Sep 02, 2014 6:57 pm
by NorbertKrupa
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.

Re: Views vs Table Access

Posted: Wed Sep 03, 2014 12:51 pm
by JimKnicely
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

Re: Views vs Table Access

Posted: Wed Sep 03, 2014 2:46 pm
by NorbertKrupa
Hmm, I need to test that out using roles though.

Re: Views vs Table Access

Posted: Wed Sep 03, 2014 6:35 pm
by JimKnicely
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)