Creating Databases for Tables and Views - Teradata Vantage

Configuring Teradata Vantage™ After Installation
Product
Teradata Vantage
Release Number
1.1
Published
July 2019
Language
English (United States)
Last Update
2019-10-11
dita:mapPath
qyu1559916244734.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4014
lifecycle
previous
Product Category
Analytical Ecosystem
Software
Teradata Vantage

Tables Database

The Tables_Database provides permanent space for tables containing data. Only privileged users or batch jobs can modify the tables and data in this database. General users cannot directly access or modify data in the Tables_Database. Tables do not have permanent space allocations, automatically taking the space required to contain data from the owning database. You can create child databases within the Tables database, dividing the Tables_Database permanent space according to the size of the tables each database contains. You can optionally create additional child databases within the Tables_Database to group tables by function or department.

Views Databases

The General_Views and Update_Views databases contain only views of database tables and macros or stored procedures. The views databases have SELECT WITH GRANT OPTION access to the Tables_Database. Views and macros provide access control and can restrict users to performing specific query and update functions. You can allocate a small amount of permanent space for stored procedures, for example 100 MB, and then adjust the allocation later using the MODIFY DATABASE statement.

For accessibility, you can create several views databases according to the needs of various departments or user groups, for example:
  • Views by department, such as Finance_Views and Purchasing_Views
  • Views by user type, such as Batch_User_Views and General_User_Views
You can also create views databases by functional layer, for example:
  • Read-write views where each view mirrors a table, usable for batch loads and updates without direct access to data tables
  • Read-only views that contain selected columns from one or more tables to combine related information from multiple tables or restrict access

Use the WITH CHECK OPTION syntax in views to control which data rows users with the appropriate privileges can modify. See CREATE VIEW WITH CHECK OPTION in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

You can create a views database for each type of user:
Type of User Typical Access Rights
General users Select and execute access to query data on the Views_Database
Privileged users Select, execute, and update access to update data through the Views_Database

Database Hierarchy

Your database environment can have a hierarchy similar to the following:
  • DBC
  • SYSDBA
    • Tables_Database
    • Update_Views
    • General_Views

Creating the Tables and Views Databases

  1. Log on as SYSDBA.
  2. Create the Tables_Database. For example:
    CREATE DATABASE Tables_Database FROM SYSDBA AS PERM=200E9;
  3. Create the General_Views database. For example:
    CREATE DATABASE General_Views FROM SYSDBA AS PERM=0;
  4. Create the Update_Views database. For example:
    CREATE DATABASE Update_Views FROM SYSDBA AS PERM=0;
  5. Grant privileges to the General_Views database on the Tables_Database. For example:
    GRANT SELECT ON Tables_Database TO General_Views WITH GRANT OPTION;
  6. Grant privileges to the Update_Views database on the Tables_Database. For example:
    GRANT SELECT, INSERT, DELETE, UPDATE ON Tables_Database 
TO Update_Views WITH GRANT OPTION;