Hide v_catalog and v_monitor at Tableau

Kaito

My customer doesn't want to display v_catalog and v_monitor at Tableau. Is it possible to hide them? i have changed Search_Path. But no luck...

s_crossman

Kaito,

There doesn't appear to be a way from within Tableau. The initial SQL it executes to gather schema and table info from Vertica includes the system tables.

dbadmin=> select * from (select 'VMart' as catalog_name, schema_name, table_name, table_type, remarks from all_tables order by table_type, catalog_name, schema_name, table_name) as vmd where catalog_name ilike E'VMart' escape E'\' and table_name ilike E'%' escape E'\';
catalog_name | schema_name | table_name | table_ty
pe | remarks

--------------+--------------+---------------------------------------+----------
VMart | v_catalog | access_policy | SYSTEM TA
BLE | Access Policy information
VMart | v_catalog | all_tables | SYSTEM TA
BLE | A complete listing of all tables and views
VMart | v_catalog | audit_managing_users_privileges | SYSTEM TA
BLE | Audit table for Managing_Users_And_Privileges category
VMart | v_catalog | branches | SYSTEM TA
BLE | List of available branches
...
VMart | store | store_dimension | TABLE
|
VMart | store | store_orders_fact | TABLE
|
VMart | store | store_sales_fact | TABLE
|

And I find no setting or toggle to change that. The system tables are part of the default Public roll grant on all users. And even if you set the default role and search path to something else Public remains.

And it's not revocable.
dbadmin=> revoke usage on schema public from foo;
NOTICE 2495: Cannot revoke "USAGE" privilege(s) for schema "public" that you did not grant to "foo"
REVOKE PRIVILEGE

I created a new user in Vertica and didn't grant anything. In vsql it had no tables visible, but in Tableau it still showed the v_ system schema tables because the underlying sql above was executed. It would have to be modified in Tableau to something like:

dbadmin=> select * from (select 'VMart' as catalog_name, schema_name, table_name, table_type, remarks from all_tables order by table_type, catalog_name, schema_name, table_name) as vmd where catalog_name ilike E'VMart' and schema_name not ilike 'v_%' escape E'\' and table_name ilike E'%' escape E'\';
catalog_name | schema_name | table_name | table_type | remark
s

--------------+--------------+-----------------------------+------------+-------

VMart | DT1_0_6_OOB | Binary_Table | TABLE |
VMart | DT1_0_6_OOB | Bool_Table | TABLE |
VMart | DT1_0_6_OOB | Char1_Table | TABLE |

I also looked at the RELEASE_SYSTEM_TABLES_ACCESS() function. Unfortunately that doesn't impact at the schema level, some tables are still accessible even if this is set.

So unless someone comes up with a creative solution this should probably be an enhancement request for Tableau to include a toggle (UI or config setting) to filter out the system views. Some of our partners' SQL client tools have this functionality.

s_crossman

Kaito,

PS of note Since Tableau 7.2 the Vertica user requires access to the all_tables system table in order to view tables in Tableau Desktop. So this may get a little more complex if you try to lock down the system tables as a Vertica level.

Hope it helps

Dingqiang

Hi @Kaito, as @s_crossman said Tableau just get all schema names from all_tables, here is an idea to meet your requirement, just create a view named all_tables under public or other default schema for specific users, eg.

dbadmin=> create or replace view public.all_tables as select * from v_catalog.all_tables where enabled_role('DBADMIN') or enabled_role('SYSMONITOR') or schema_name not in ('v_catalog', 'v_monitor') ;
CREATE VIEW
dbadmin=> grant all on public.all_tables to public;
GRANT PRIVILEGE

testor=> select distinct schema_name from all_tables;
 schema_name 
-------------
 public
(1 row)

s_crossman

Dingqiang,

Nice workaround. Seems to work and allows DBADMIN to still see the v_* schemas but not non super users.