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.
- Views by department, such as Finance_Views and Purchasing_Views
- Views by user type, such as Batch_User_Views and General_User_Views
- 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.
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
- DBC
- SYSDBA
- Tables_Database
- Update_Views
- General_Views
Creating the Tables and Views Databases
- Log on as SYSDBA.
- Create the Tables_Database. For example:
CREATE DATABASE Tables_Database FROM SYSDBA AS PERM=200E9;
- Create the General_Views database. For example:
CREATE DATABASE General_Views FROM SYSDBA AS PERM=0;
- Create the Update_Views database. For example:
CREATE DATABASE Update_Views FROM SYSDBA AS PERM=0;
- Grant privileges to the General_Views database on the Tables_Database. For example:
GRANT SELECT ON Tables_Database TO General_Views WITH GRANT OPTION;
- 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;