From DBeaver, how to filter system schemas (ie: starting with "%") ?

Question

Question

Sylvain Guilbaud · Aug 28, 2023

#JDBC #SQL #InterSystems IRIS #InterSystems IRIS for Health

I'm looking for an efficient way in DBeaver to filter system tables (ex: belonging to a schema starting with "%").

By using a user with the %All role, DBeaver shows us a long list of system schemas, which forces us to go down the list before accessing the user tables.

By creating a user in IRIS with restricted rights, this list can be reduced, but the interest of the %All role is lost.

Product version: IRIS 2023.2

$ZV: IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2023.2 (Build 227U) Mon Jul 31 2023 17:43:25 EDT

Go to the original post written by @Sylvain Guilbaud

Discussion (8)2

Log in or sign up to continue

Sylvain Guilbaud · Aug 28, 2023

The solution is to use the Schemas / Users filters in the connection settings :

And adding "%" in the "to exclude" part :

1 0

Sylvain Guilbaud · Aug 28, 2023

Actually excluding all the "%" schemas in the connection setting is not working properly as it masks all the schemas' names. You have directly the names of all the tables and when you try to open it, you get an error as the query use SQLUser as the default schema.

0 0

Dmitry Maslennikov · Aug 28, 2023

I think we still need deeper support for IRIS in DBeaver, and it can be implemented.

So it will be possible to have more options to be configured, and more possibilities

0 0

Sylvain Guilbaud · Aug 28, 2023

I agree.
Filtering "schemas / users" by excluding "%" it not working very well indeed as it masks all the schemas' names and then considers all the tables in the "SQLUser" default schema...

0 0

Jeffrey Drumm · Aug 28, 2023

You can explicitly exclude '%' schemas with the pattern "\%*" in the Exclude list:

3 0

Sylvain Guilbaud · Aug 28, 2023

Thank you @Jeffrey Drumm
I missed the correct syntax !

1 0

Eduard Lebedyuk · Aug 29, 2023

Alternative approach: create a user which has access only to schemas you need.

1 0

score 0 · Answer 1 · 2023-08-30T10:16:26-04:00

This was typically my first thinking when I said "by creating a user in IRIS with restricted rights, this list can be reduced, but the interest of the %All role is lost."