USAGE privilege for schemas

Detail name	Value
Changelog Number	9685
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.0.0
Resolution Date	2020-09-11

Background

Before version 6.2 every user could see all schemas (including virtual schemas) independently of access privileges or ownership. The solution in version 6.2 (see EXASOL-2662) does not scale (see EXASOL-2628), so we propose a new scalable and fast solution: the USAGE privilege for schemas.

Solution

The usage object privilege can only be granted on schemas and is necessary for any other object privilege in the schema. If a user doesn't have USAGE on a schema all other object privileges don't have the desired effect:

--DBA:
GRANT SELECT ON SCHEMA1.TABLE1 TO USER1;

--USER1:
SELECT * FROM SCHEMA1,TABLE1;
--> Error: no USAGE granted on SCHEMA1 for user USER1

--DBA
GRANT USAGE ON SCHEMA1 TO USER1;

--USER1:
SELECT * FROM SCHEMA1,TABLE1;
--> ok

Furthermore we introduce a system privilege USE ALL SCHEMAS that enables the grantee to see all schemas and hence execute all object privileges he has. This is the equivalent of the behavior before the introduction of USAGE.
During migration to 7.0 and at first start of a new 7.0 instance, USE ALL SCHEMAS is granted to public. By doing this USAGE is no changed behavior by default.

How to hide schemas

If you want to hide schemas for users you have to

• REVOKE USE ANY SCHEMA FROM PUBLIC
• For all users and roles GRANT USAGE for every schema the user/role has any object privilege in. We provide a script (SYS.GRANT_USAGE_PRIVS) that supports DBAs in executing this task.

By doing this every user can only see the schemas he has object privileges in. Keep in mind that for every future object privilege grant, you have to check if USAGE is granted on the containing schema.

Changed behavior

* Parameter -extendedSchemaVisibility has been removed. * System privilege CREATE ANY SCRIPT does not give access to all scripts any more (via EXA_ALL_SCRIPTS)