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
andREFRESH
, authentication details such as username and password must usually be included in the connection object. TheACCESS
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.