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

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

select * from users where user_name ilike 'tableau1';

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

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

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

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.

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;

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.

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=>

Thank you SruthiA.
I will use - DEFAULT ROLE ALL