The privileges required to create and administrate the adapter scripts and virtual schemas are available in the SQL commands CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, and CREATE SCRIPT. For additional details, see Details on Rights Management.
Encapsulate Access Credentials through Connections
Exasol recommends to use connection objects to encapsulate the security related information. In the following example, you only define the connection name but not the actual credentials.
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>';The adapter script should support this and extract the credentials from the connection to establish a connection to the external system.
The administrator of the virtual schema needs the following privileges to enable the encapsulation through connections:
- You need to grant connection to the administrator (GRANT CONNECTION). This is required because in most cases, an adapter script internally generates an IMPORT statement in two-phased execution (for further details, see How it works) that uses the connection like standard IMPORT statement. IMPORT is not an internal Exasol command and not a public script, therefore, the credentials cannot be extracted. To execute the IMPORT statement, the system privilege IMPORT is required.
- In most cases, you need the connection details such as user and password. This is required because the adapter script needs a direct connection to read metadata from the external system for commands such as CREATE and REFRESH. For this requirement, a special ACCESS privilege is introduced. This privilege protects the connection details data. By using the statement GRANT ACCESS ON CONNECTION [FOR SCRIPT] you can limit the access only to a specific script (FOR SCRIPT clause) and ensure that the administrator cannot access that data (by creating a new script which extracts and simply returns the credentials). The user should be allowed to execute the script but not to alter the script.
In the following example, the administrator gets the appropriate privileges to create a virtual schema by using the adapter script (jdbc_adapter) and a 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
The access to virtual data works in similar way to the creation of view by granting SELECT privilege for a virtual schema. For virtual schema, you can grant this for the complete schema through GRANT SELECT ON SCHEMA. Alternatively, the user is the owner of the schema.
The user is also required to have the USAGE privilege on the virtual schema.
Internally, this works similar to views because the check for privileges is executed in the name of the script owner. Because of that, the details are completely encapsulated (the access to adapter scripts and the credentials to the external system).
Limit Access to Specific Tables
If you don't want to grant full access for a virtual schema but only for certain tables, you can do that by the following alternatives:
- Views: Instead of granting direct access to the virtual schema you can also create views on that data and provide indirect access for certain users.
- Logic within the adapter script: It is possible to solve the requirement in the adapter script. For example,you can use the parameter TABLE_FILTER to define a list of tables that should be visible (for details, see https://github.com/exasol/virtual-schemas). If this virtual schema property is not defined, then all available tables are visible.
