Grant Issue when using one Username from the Apps to vertica

Gil_Peretz

Hello, I am looking for a simple solution to grant issues... We have multi-tenant data warehouse, each Tenant has its own schema. We are using two main users, to access Vertica from our Apps. 1. Superuser for Etl’s and Batch runs 2. Read Only user with grant select on all tables for Queries only. we have two connections pool in the Apps : 1. batch processing 2. Analytic Queries Each using its own resource pool, and using different Username. The READ_ONLY username has GRANT SELECT an all schemas/objects. on the READ_ONLY connection pool, we set the search_path for every Query. The problem: if the Query will use . in the FROM SQL clause, he will be able to "see" other tenant schema. I am wondering if there is a simple solution, how to overcome this matter without parsing the SQL code, and removing the . leaving it only with . -------------------------------- Gil Peretz , 054-5597107

Sharon_Cutter

You could use roles for this. Create one role for each schema and grant usage on the schema and select on all tables to the role. Grant all of the roles to the read_only user, but not as default roles. Then use SET ROLE to enable the one that should be enabled, SET ROLE NONE to return to no roles enabled. create schema user1; create schema user2; create table user1.t(c1 varchar(100)); create table user2.t(c1 varchar(100)); grant usage on schema user1 to user1_role; grant usage on schema user2 to user2_role; grant select on all tables in schema user1 to user1_role; grant select on all tables in schema user2 to user2_role; create user testuser; grant user1_role, user2_role to testuser; select all_roles, default_roles from users where user_name='testuser' As testuser: show enabled roles; -- no privs select * from user1.t; select * from user2.t; set role user1_role; show enabled roles; select * from user1.t; -- no privs on user2 select * from user2.t; set role none; set role user2_role; -- no privs on user1 select * from user1.t; select * from user2.t;

Gil_Peretz

Thank You Sharon, looks like a valid idea. i'll try it....I'll keep you posted