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

ilya2ilya2
edited March 2022 in General Discussion

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)
Tagged:

Answers

  • SruthiASruthiA Vertica Employee Administrator

    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
    
  • SruthiASruthiA Vertica Employee Administrator

    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
    
  • DaveTDaveT Vertica Employee Employee

    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;
    
  • DaveTDaveT Vertica Employee Employee

    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.

  • SruthiASruthiA Vertica Employee Administrator

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file