Permission denied for view. How to give SELECT rights to a view via role?

ilya2

How to give SELECT rights to a view via role?
Version Vertica: v11.1.0-0

➜  ~ /opt/vertica/bin/vsql -U dbadmin -d VMart -p 5433 -h 127.0.0.1 
VMart=> create user tableau1 IDENTIFIED BY '111111';
CREATE USER
VMart=> create user tableau2 IDENTIFIED BY '111111';
CREATE USER
VMart=> create user tableau3 IDENTIFIED BY '111111';
CREATE USER
VMart=> CREATE SCHEMA DDS default include privileges;
CREATE SCHEMA
VMart=> CREATE ROLE dds_reader;
CREATE ROLE
VMart=> GRANT USAGE ON SCHEMA DDS to dds_reader;
GRANT PRIVILEGE
VMart=> GRANT SELECT ON SCHEMA DDS to dds_reader with grant option;
GRANT PRIVILEGE
VMart=> GRANT dds_reader TO tableau1, tableau2, tableau3 WITH ADMIN OPTION;
GRANT ROLE
VMart=> create table dds.test1 (id int);
WARNING 6978:  Table "test1" will include privileges from schema "DDS"
CREATE TABLE
VMart=> alter table dds.test1 owner to tableau2;
ALTER TABLE
VMart=> create view dds.v_test1 as select * from dds.test1;
WARNING 7070:  View "v_test1" will include privileges from schema "DDS"
CREATE VIEW
VMart=> alter view dds.v_test1 owner to tableau3;
ALTER VIEW
VMart=> \c VMart tableau1
Password:
You are now connected to database "VMart" as user "tableau1".
VMart=> set role dds_reader;
SET
VMart=> select * from dds.test1;
 id
----
(0 rows)

VMart=> select * from dds.v_test1;
ERROR 4367:  Permission denied for relation v_test1

If you do not use ROLE, then it works.

VMart=> create user tableau1 IDENTIFIED BY '111111';
CREATE USER
VMart=> create user tableau2 IDENTIFIED BY '111111';
CREATE USER
VMart=> create user tableau3 IDENTIFIED BY '111111';
CREATE USER
VMart=> CREATE SCHEMA DDS default include privileges;
CREATE SCHEMA
VMart=> GRANT USAGE, SELECT ON SCHEMA DDS to tableau1, tableau2, tableau3;
GRANT PRIVILEGE
VMart=> GRANT SELECT ON SCHEMA DDS to tableau3 with grant option;
GRANT PRIVILEGE
VMart=> create table dds.test1 (id int);
WARNING 6978:  Table "test1" will include privileges from schema "DDS"
CREATE TABLE
VMart=> alter table dds.test1 owner to tableau2;
ALTER TABLE
VMart=> create view dds.v_test1 as select * from dds.test1;
WARNING 7070:  View "v_test1" will include privileges from schema "DDS"
CREATE VIEW
VMart=> alter view dds.v_test1 owner to tableau3;
ALTER VIEW
VMart=> \c VMart tableau1
Password:
You are now connected to database "VMart" as user "tableau1".
VMart=> select * from dds.test1;
 id
----
(0 rows)

VMart=> select * from dds.v_test1;
 id
----
(0 rows)

SruthiA

What is the default role for the user tableau1? What is the vertica version?

select * from users where user_name ilike 'tableau1';

ilya2

Version Vertica: v11.1.0-0

VMart=> select * from users where user_name ilike 'tableau1';
-[ RECORD 1 ]---------+----------------------------------------------------------
user_id               | 45035996273846248
user_name             | tableau1
is_super_user         | f
profile_name          | default
is_locked             | f
lock_time             |
resource_pool         | general
memory_cap_kb         | unlimited
temp_space_cap_kb     | unlimited
run_time_cap          | unlimited
max_connections       | unlimited
connection_limit_mode | database
idle_session_timeout  | unlimited
grace_period          | undefined
all_roles             | dds_reader*
default_roles         |
search_path           | "$user", public, v_catalog, v_monitor, v_internal, v_func
ldap_dn               |
ldap_uri_hash         | 0
is_orphaned_from_ldap | f

SruthiA

could you please run alter user tableau1 default role dds_reader and see if it helps?

ilya2

Did not help =(

VMart=> alter user tableau1 default role dds_reader;
ALTER USER
VMart=> \c VMart tableau1
Password:
You are now connected to database "VMart" as user "tableau1".
VMart=> select * from dds.test1;
 id
----
(0 rows)
VMart=> select * from dds.v_test1;
ERROR 4367:  Permission denied for relation v_test1
VMart=> \x
Expanded display is on.
VMart=> select * from users where user_name ilike 'tableau1';
-[ RECORD 1 ]---------+----------------------------------------------------------
user_id               | 45035996273846248
user_name             | tableau1
is_super_user         | f
profile_name          | default
is_locked             | f
lock_time             |
resource_pool         | general
memory_cap_kb         | unlimited
temp_space_cap_kb     | unlimited
run_time_cap          | unlimited
max_connections       | unlimited
connection_limit_mode | database
idle_session_timeout  | unlimited
grace_period          | undefined
all_roles             | dds_reader*
default_roles         | dds_reader*
search_path           | "$user", public, v_catalog, v_monitor, v_internal, v_func
ldap_dn               |
ldap_uri_hash         | 0
is_orphaned_from_ldap | f

DaveT

Try doing what Sruthi suggested except do it for user tableau3 (the owner of the view) instead: alter user tableau3 default role dds_reader;

Then try to select from the view as one of the other users by first setting the role dds_reader or also making it their default role.

ilya2

alter user tableau3 default role dds_reader;

That's how it worked. Thanks

Can I somehow activate the available roles in the default role for all users?

Something like:

ALTER DATABASE VMart SET PARAMETER EnableAllRolesOnLogin=1;

DaveT

That will remove the requirement to issue SET ROLE. But it doesn't appear that will allow the view access to work. It appears that the specific role enablement is checked to ensure that the view owner has SELECT WITH GRANT OPTION on the base table.

SruthiA

As Dave mentioned EnableAllRolesOnLogin parameter will not help. One thing you can do is making all roles as default roles to the user once they are granted.

dbadmin=> create user user1;
CREATE USER
dbadmin=> create role test1;
CREATE ROLE
dbadmin=> grant test1 to user1;
GRANT ROLE

select set_config_parameter('EnableAllRolesOnLogin',1);
-[ RECORD 1 ]--------+---------------------------
set_config_parameter | Parameter set successfully

dbadmin=> SELECT user_name, default_roles, all_roles FROM users WHERE user_name = 'user1';
user_name | default_roles | all_roles
-----------+---------------+-----------
user1 | | test1
(1 row)

dbadmin=>

dbadmin=> ALTER USER user1 DEFAULT ROLE ALL;
ALTER USER

dbadmin=> SELECT user_name, default_roles, all_roles FROM users WHERE user_name = 'user1';
user_name | default_roles | all_roles
-----------+---------------+-----------
user1 | test1 | test1
(1 row)

dbadmin=>

ilya2

Thank you SruthiA.
I will use - DEFAULT ROLE ALL