Virtual Schema Privileges

This article describes the privileges required to create and use virtual schemas and adapters.

The general privileges required to create and administrate adapter scripts and virtual schemas are defined using the SQL commands CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, and CREATE SCRIPT. This article describes how to manage security in connections to data sources, and how to manage access to virtual schemas.

For more details about managing privileges, see Details on Rights Management.

Encapsulate access credentials in connections

We recommend that you use connection objects to encapsulate security related information such as authentication details. In the following example, you define the connection name but not the actual credentials. The adapter script must then extract the credentials from the connection object to establish a connection to the external system.

CREATE CONNECTION Exasol_CONNECTION 
  TO 'jdbc:exa:<host>:<port>' 
  USER '<user>' 
  IDENTIFIED BY '<password>';

CREATE VIRTUAL SCHEMA <virtual schema name> 
USING ADAPTER.JDBC_ADAPTER 
WITH
    CONNECTION_NAME = 'EXASOL_CONNECTION'
    SCHEMA_NAME     = '<schema name>';

Required privileges

To use connections with encapsulated security information in a virtual schema, the administrator of the virtual schema must be granted certain privileges by a system administrator.

• To allow the virtual schema administrator to use the connection in a virtual schema, use GRANT CONNECTION <CONNECTION NAME> TO <USER/ROLE>.

  An adapter script will (in most cases) internally generate an IMPORT statement in a two-step execution, which uses the connection in the same way as a standard IMPORT statement would. Note that to execute the IMPORT statement, the system privilege IMPORT is required.

• To restrict access to security information in the connection, use GRANT ACCESS ON CONNECTION <CONNECTION NAME> TO <USER/ROLE>.

  Because the adapter script needs a direct connection to read metadata from the external system for commands such as CREATE and REFRESH, authentication details such as username and password must usually be included in the connection object. The ACCESS privilege restricts access to the connection details to the granted user/role.

  To allow access to connection details only for a specific script, use GRANT ACCESS ON CONNECTION <CONNECTION NAME> FOR <SCRIPT NAME> TO <USER/ROLE>. This ensures that a user cannot access the details indirectly by creating a new script that extracts and returns the credentials. The user should be allowed to execute the script, but not to alter the script.

Example

In the following example, the administrator user_exasol_access is granted the necessary privileges to create a virtual schema using the adapter script jdbc_adapter and the connection EXASOL_CONNECTION.

GRANT CREATE VIRTUAL SCHEMA TO user_exasol_access;
GRANT EXECUTE ON SCRIPT adapter.jdbc_adapter TO user_exasol_access;
GRANT CONNECTION EXASOL_CONNECTION TO user_exasol_access;
GRANT ACCESS ON CONNECTION EXASOL_CONNECTION
FOR SCRIPT adapter.jdbc_adapter TO user_exasol_access;

Grant access on virtual tables

Access to virtual data is managed by granting the SELECT privilege for a virtual schema. You can grant this privilege for the complete schema using GRANT SELECT ON SCHEMA. The user must have the USAGE privilege on the virtual schema or be the owner of the schema.

Internally, this works similar to views, because the check for privileges is executed in the name of the script owner. The details are therefore completely encapsulated (the access to adapter scripts and the credentials to the external system).

Limit access to specific tables

To grant access only to certain tables instead of the whole virtual schema, you can use the following methods:

Views

Instead of granting direct access to the virtual schema, create views on that data and provide access only to the views.

Logic in the adapter script

For example, use the parameter TABLE_FILTER to define a list of tables that should be visible. If this virtual schema property is not defined, then all available tables are visible.

For more details, see https://github.com/exasol/virtual-schemas.