DDL VDB

A Virtual Database (VDB) can created through DDL statements. Teiid Spring Boot supports the SQL-MED specification to utilize foreign data sources.

DDL captures information about the VDB - the sources it integrate, and preferences for importing metadata. DDL may be deployed as a single file or as a set of files in a zip archive.

Example VDB DDL
CREATE DATABASE my_example;
USE DATABASE my_example;

CREATE SERVER pgsql
    VERSION 'one' FOREIGN DATA WRAPPER postgresql
    OPTIONS (
        "resource-name" 'java:/postgres-ds'
    );

CREATE SCHEMA test SERVER pgsql;
IMPORT FOREIGN SCHEMA public FROM SERVER pgsql INTO test
    OPTIONS(
        importer.tableTypes 'TABLE,VIEW'
);

DDL File Format

For compatibility with the existing metadata system, DDL statements must appear in a specific order to define a virtual database. All of the database structure must be defined first - this includes create/alter database, domains, vdb import, roles, and schemas statements. Then the schema object, schema import, and permission DDL may appear.

Create a Database

Every VDB file must start with database definition where it specifies the name and version of the database. The create syntax for database is

CREATE DATABASE {db-name}  OPTIONS ( <options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

An example statement

CREATE DATABASE my_example  OPTIONS ("cache-metadata" true);

For the list of database scoped properties see VDB properties.

Immediately following the create database statement is an analogous use database statement.

As we learned about the VDB components earlier in the guide, we need to first create translators, then connections to data sources, and then using these we can gather metadata about these sources. There is no limit on how many translators, or data sources or schemas you create to build VDB.

Create a Translator

A translator is an adapter to the foreign data source. The creation of translator in the context of the VDB creates a reference to the software module that is available in the Teiid Spring Boot system. Some of the examples of available translators include:

  • oracle

  • mysql

  • postgresql

  • mongodb

CREATE FOREIGN ( DATA WRAPPER | TRANSLATOR ) {translator-name}
    [ TYPE {base-translator-type} ]
    [ OPTIONS ( <options-clause>) ]

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

Optional TYPE is used to create an "override" translator. It is not required to define translators already known to the engine with a CREATE - for example CREATE FOREIGN DATA WRAPPER oracle OPTIONS …​ - will effectively be ignored.

The OPTIONS clause is used to provide the "execution-properties" of a specific translator defined in either in {translator-name} or {base-translator-name}. These names MUST match with available Translators in the system. link:as_translators.adoc[Translators} documents all the available translators.

For all available translators see Translators

Example Creating Override Translator
CREATE FOREIGN DATA WRAPPER oracle-override TYPE oracle OPTIONS (useBindVariables false);

The above example creates a translator override with an example showing turning off the prepared statements.

Additional management support to alter, delete a translator

ALTER  (DATA WRAPPER|TRANSLATOR) {translator-name} OPTIONS (ADD|SET|DROP <key-value>);

DROP FOREIGN [<DATA> <WRAPPER>|<TRANSLATOR>] {translator-name}

Associate The Translator With A Source

The SERVER construct is used to associate your translator with a data source.

CREATE SERVER {source-name} [TYPE '{source-type}']
    [VERSION '{version}'] FOREIGN DATA WRAPPER {translator-name}
    OPTIONS (<options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

Name

Description

source-name

Name given to the source’s connection.

source-type

Not currently used.

translator-name

Name of the translator to be used with this server.

options

Currently only resource-name is supported. resource-name provides a way to specify the environmentally dependent (JNDI or bean) name of the source if it differs from the server name. For example java:/source
Example 3: creating a data source connection to Postgres database
CREATE SERVER pgsql
    FOREIGN DATA WRAPPER postgresql
    OPTIONS (
        "resource-name" 'java:/postgres-ds'
    );

An example file source.

Example 4: creating a data source connection to "file" resource adapter.
CREATE SERVER marketdata
    FOREIGN DATA WRAPPER file
    OPTIONS(
        ParentDirectory '/path/to/marketdata'
    );

Additional management support to alter/delete a connection.

ALTER  SERVER {source-name}  OPTIONS ( ADD|SET|DROP <key-value>);
DROP SERVER  {source-name};

Now that we have the Translators and Connections created, the next step is to create SCHEMAs and work with metadata.

Create SCHEMA

A schema is a container for metadata. It works as a namespace in which metadata objects like TABLES, VIEWS and PROCEDURES exist. The below DDL shows how to create a SCHEMA element.

CREATE [VIRTUAL] SCHEMA {schema-name}
    [SERVER {server-name} (<COMMA> {server-name})*]
    OPTIONS (<options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

  • The use of VIRTUAL keyword defines if this schema is "Virtual Schema". In the absence of the VIRTUAL keyword, this Schema element represents a "Source Schema". Refer to VDB Guide about different types of Schema types.

Important
 If the Schema is defined as "Source Schema", then SERVER configuration must be provided, to be able to determine the data source connection to be used when executing queries that belong to this Schema.

Below are typical properties that can be configured for a Schema in the OPTIONS clause.

Name

Description

VISIBLE

Set to false to make the Schema not visible to metadata interrogation

ANNOTATION

A description of the Schema
Example 5: Showing to create a source schema for PostgreSQL server from example above
CREATE SCHEMA test SERVER pgsql;

Additional management support to alter/delete a schema can be done through following commands.

ALTER [VIRTUAL] SCHEMA {schema-name} OPTIONS (ADD|SET|DROP <key-value>);
DROP SCHEMA {schema-name};

Importing Schema

If you are designing a source schema, you can add the TABLES, PROCEDURES manually to represent the data source, however in certain situations this can be tedious, or complicated. For example, if you need to represent 100s of existing tables from your Oracle database in Teiid Spring Boot? Or if you are working with MongoDB, how are you going to map a document structure into a TABLE? For this purpose, Teiid Spring Boot provides an import metadata command, that can import/create metadata that represents the source. The following command can be used for that purpose with most of the sources (LDAP source is only exception, not providing import)

IMPORT [FOREIGN SCHEMA {foreign-schema-name}]
    FROM (SERVER {server-name} | REPOSITORY {repository-name})
    INTO {schema-name}
    OPTIONS (<options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

foreign-schema-name : Name of schema to import. Typically most databases are tied to a schema name, like "public", "dbo" or name of the database. If you are working with a non-relational source, or a DDL file, you can provide a dummy value here or omit the entire FOREIGN SCHEMA clause. server-name: name of the server created above to import metadata from. repository-name: Custom/extended "named" repositories from which metadata can be imported. See MetadataRepository interface for more details. Teiid Spring Boot provides a built in type called "DDL-FILE" see example below. schema-name: The foreign schema name to import from - it’s meaning is up to the translator. import qualifications : using this you can limit your import of the Tables from foreign datasource specified to this list. options-clause : The "importer" properties that can be used to refine the import process behavior of the metadata. Each Translator defines a set of "importer" properties with their documentation or through extension properties.

The below example shows importing metadata from a PostgreSQL using server example above.

Example Import
-- import from native database
IMPORT FOREIGN SCHEMA public
    FROM SERVER pgsql
    INTO test

The above command imports public.customers, public.orders tables using pgsql’s connection into a VDB schema test.

Example Import
-- in archive based vdbs(.vdb) you can provide schema in separate files and pull them in a main vdb.ddl file as:
IMPORT FROM REPOSITORY "DDL-FILE"
    INTO test OPTIONS ("ddl-file" '/path/to/schema1.ddl')
IMPORT FROM REPOSITORY "DDL-FILE"
    INTO test OPTIONS ("ddl-file" '/path/to/schema2.ddl')
Tip
 The example IMPORT SCHEMA can be used with any custom Metadata Repository, in the REPOSITORY DDL-FILE, DDL-FILE represents a particular type of repository.

Create Schema Objects

Most DDL statements that affect schema objects need the schema to be explicitly set. To be able to establish the schema context you are working with use following command:

Example: Set Schema
SET SCHEMA {schema-name};

then you will be create/drop/alter schema objects for that schema.

Example: Schema Object Creation
SET SCHEMA test;
CREATE VIEW my_view AS SELECT 'HELLO WORLD';

Data Roles

Data roles, also called entitlements, are sets of permissions defined per VDB that dictate data access (create, read, update, delete). Data roles use a fine-grained permission system that Teiid Spring Boot will enforce at runtime and provide audit log entries for access violations. To read more about Data Roles and Permissions see Data Roles and Permissions

Here we will show DDL support to create these Data Roles and corresponding permissions.

BNF for Create Data Role
CREATE ROLE {data-role} WITH
    FOREIGN ROLE {enterprise-role}(,{enterprise-role})*
    | ANY AUTHENTICATED

data-role: Data role referenced in the VDB enterprise-role: Enterprise role(s) that this data-role represents WITH ANY AUTHENTICATED: When present, this data-role is given to any user who is valid authenticated user.

Example: Create Data Role
CREATE ROLE readWrite WITH FOREIGN ROLE developer,analyst;

CREATE ROLE readOnly WITH ANY AUTHENTICATED;
Note
 Roles must be defined as a structural component of the VDB. GRANT/REVOKE may then appear after all of the database structure has been defined.

See Permissions for more details on the permission system.

BNF for GRANT/REVOKE command
GRANT [<permission-types> (,<permission-types>)* ]
    ON (<grant-resource>)
    TO {data-role}

GRANT (TEMPORARY TABLE | ALL PRIVILEGES)
    TO {data-role}

GRANT USAGE ON LANGUAGE {language-name}
    TO {data-role}

<permission-types> ::=
    SELECT | INSERT |  UPDATE | DELETE |
    EXECUTE | ALTER | DROP

<grant-resource> ::=
        TABLE {schema-name}.{table-name} |
        PROCEDURE {schema-name}.{procedure-name} |
        SCHEMA {schema-name} |
        COLUMN {schema-name}.{table-name}.{column-name} [MASK [ORDER n] {expression} ]

REVOKE [(<permission-types> (,<permission-types>)* )]
    ON (<revoke-resource>)
    FROM {data-role}

REVOKE
    (TEMPORARY TABLE | ALL PRIVILEGES)
    FROM {data-role}

REVOKE USAGE ON LANGUAGE {language-name}
    FROM {data-role}

<revoke-resource> ::=
        TABLE {schema-name}.{table-name} |
        PROCEDURE {schema-name}.{procedure-name} |
        SCHEMA {schema-name} |
        COLUMN {schema-name}.{table-name}.{column-name} [MASK]

  • permission-types: Types of permissions to be granted

  • language-name: Name of the language

  • grant-resource: This is Schema element in the VDB on which this grant applies to.

  • revoke-resource: This is Schema element in the VDB on which this revoke applies to. Specifying the CONDITION or MASK keyword will attempt to move the specific CONDITION or MASK for that resource.

  • schema-name: Name of the schema this resource belongs to

  • table-name: Name of the Table/View

  • procedure-name: Procedure Name

  • column-name: Name of the column

  • expression: any valid sql expression, this can include columns from referenced resource

BNF for CREATE POLICY and DROP POLICY.

Warning
 GRANT/REVOKE mostly function as direct replacements for the legacy permission model. They do not function the same as standard SQL GRANT/REVOKE. GRANT/REVOKE apply/remove permissions from the given resource - but do not affect prior GRANT/REVOKEs against any other resource. For example if you GRANT the select permission on a table, then REVOKE the select permission on the table’s schema, the GRANT of the select permission will remain on the table. At runtime GRANTs are still interpreted hierarchically - a select GRANT on a schema implies read access to all contained schema objects. GRANT/REVOKE is also not ADD/DROP aware. If the GRANT target is dropped the old GRANT still remains and could affect any recreated object.
Warning
 POLICIES are not ADD/DROP aware. If the POLICY target is dropped the old POLICY still remains and could affect any recreated object.
Example: Give insert, select, update permission on single table to user with enterprise role "role1"
CREATE ROLE RoleA WITH FOREIGN ROLE role1;
...
GRANT INSERT, SELECT, UPDATE ON TABLE test.Customer TO RoleA;
Example : Give all permissions to user with "admin" enterprise role
CREATE ROLE everything WITH FOREIGN ROLE admin;
...
GRANT ALL PRIVILEGES TO everything;
Example : All users can see only Orders table contents amount < 1000
CREATE ROLE base_role WITH ANY AUTHENTICATED;
...
GRANT SELECT ON TABLE test.Orders TO base_role;
CREATE POLICY policyOrders ON test.Orders TO base_role USING (amount < 1000) TO base_role;
Example : Override previous example to more privileged user.
CREATE POLICY policyRoleAOrders ON test.Orders TO RoleA USING (amount < 1000 and amount >=1000);
Example : Restricting rows to only those owned by this user.
GRANT SELECT ON TABLE test.CustomerOrders TO RoleA;
CREATE POLICY policyCustomerOrders ON test.CustomerOrders TO RoleA USING (name = user());

In the above example, user() function returns the currently logged in user id, if that matches to the name column, only those rows will be returned. There are functions like hasRole('x') that can be used too.

Example : Column Masking, mask "amount for all users"
GRANT SELECT ON COLUMN test.Order.amount
    MASK 'xxxx'
    TO base_role;
Example : Column Masking, mask "amount for all users when amount > 1000"
GRANT SELECT ON COLUMN test.Order.amount
    MASK 'CASE WHEN amount > 1000 THEN 'xxxx' END'
    TO base_role;
Example : Column Masking, mask "amount for all users" except the calling user is equal to the user()
GRANT SELECT ON COLUMN test.Order.amount
    MASK 'xxxx'
    CONDITION 'customerid <> user()'
    TO base_role;

results matching ""

    No results matching ""