How to unlist system catalog views from table list

[matt.rankin]

Question

When connecting to an external database (via ADO in this case), is it possible to unlist or filter out the internal database objects from Base’s own list of tables/queries?

Context

I have just created an empty database in MSSQL Express (2008), along with a dedicated login for Base to connect with. The connection is successful, however the database table list in Base includes the enormous catalog of system views:

Bear in mind, these are not even tables, they are readonly system views. And there are hundreds of them, finding the table I want would be maddening. IMO it is not practical to use Base at all under these conditions.

What I’ve tried

• As mentioned, I created a separate login for Base to use; not an administrative account.
• Tried unchecking “Ignore the privileges from the database driver” setting under Advanced Settings.
• Mapped login to a user within the database that has only been granted basic CRUD permissions on a specially created schema, so in theory Base should only have explicit access to a schema with no objects created in it yet. I have also set this as the user’s default_schema.
• Denied permissions on the sys schema to the user.

[RobertG]

Could be set in Tools → Table Filter….

[matt.rankin]

That does indeed work, though I’d consider it more of a workaround than an ideal solution (no offence to you at all, the solution will likely require a software update to Base). The table filter list will become cumbersome to maintain once I start adding views.

This looks like an area of potential improvement if Base could filter out—or at least differentiate—system objects from user-created ones. If anything else, it’s a poor default from a UX perspective. This would only be useful in incredibly rare niche scenarios.

[RobertG]

Table filter will also hide views, if you want to hide them. But it doesn’t hide complete databases, if you connect, for example, to a MariaDB server and see all databases you have permissions to. So you could see there is a database available, but there seems to be no entry.

I don’t know anything about MSSQL, but in all server databases I have used (MySQL/MariaDB, PostgreSQL, Firebird …) there are no system tables in the special default-schema for the user. All other system tables could be hidden by table filter with one click on the schema, which contains the system tables.

[matt.rankin]

As you can see from the screenshot I provided, Base doesn’t display the schema-qualified name, but they are mostly/all under the sys schema. By default, objects in MSSQL are created under the dbo schema. So the database engine separates them by schema, I just can’t see any way of preventing the “leak” so to speak of these system objects other than to filter them out by name as per the method you showed me.

[RobertG]

Seems to be a problem of the driver for MSSQL databases:

Different databases shown in MariaDB. This are the databases the user could access to.

Different schema shown in PostgreSQL.

MariaDB and PostgreSQL are connected with direct connection (Driver supported by Base). But it will be nearly the same with JDBC. With JDBC for PostgreSQL the schema “information_schema” and “pg_catalog” won’t be shown by default for the same user.

[matt.rankin]

Firstly, I appreciate the time you’ve spent looking into this. Secondly, the ODBC connection type seems a lot better to work with (which I’ve done now), as it shows the object hierarchy, so I can just drill into dbo and ignore the system objects:

I now have other problems (not being able to modify data in any way), but that’s another story and not for this question.