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
Views vs Table Access
Moderator: NorbertKrupa
Re: Views vs Table Access
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
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Views vs Table Access
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.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Views vs Table Access
norbertk, I am not sure that this statement is true:
Setup:
Now when I log in as the user jane, I can SELECT from the view, but not the table:
Example...views in Vertica require privileges on the tables involved in the view
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
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Views vs Table Access
Hmm, I need to test that out using roles though.
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Views vs Table Access
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.