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)