EXA_SCHEMAS is slow with very many USAGE grants
Details
| Detail name | Value |
|---|---|
| Changelog Number | 15275 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0 |
| Fix Versions | Exasol 7.1.13, Exasol 8.4.0 |
| Resolution Date | 2022-08-24 |
Background
Exasol provides the USAGE privilege to manage the visibility of schemas to users.
By default, every user can see any schema. To restrict visibility, you need to run
revoke use any schema from public;
and then grant usage to specific users like the following:
grant usage on schema1 to user1;
Description
After enabling USAGE, accessing the system table EXA_SCHEMAS can be substantially slower, if schema level usage is granted to a huge number of different users or roles (>20000).
In other words, runtime increases roughly proportional to:
select COUNT(distinct grantee) from EXA_DBA_OBJ_PRIVS where object_type = 'SCHEMA' and privilege='USAGE';
Fix
Accessing EXA_SCHEMAS will always be fast.
