17.10 - Using Views To Control Access To The Partitions of a PPI Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

You can use views to control access to the partitions of a PPI table. For instance, create a view for SELECT ACCESS to the table and another view for data maintenance. Conditions in the views can restrict access to only the appropriate partitions. The views can be replaced to vary the conditions. Grant privileges only on the views.

If you define a view on a PPI table, that view does not contain the system-derived PARTITION column or the system-derived PARTITION#L n columns, where the value of n ranges from 1 - 15, inclusive, unless you specify it explicitly in the select list of its definition.

In general, you should not include the system-derived PARTITION or PARTITION#L n columns from the underlying tables in view definitions to prevent users from referencing them. A better strategy is to specify conditions on the partitioning columns, because the values of PARTITION (or a given PARTITION#L n) might change for rows if the partitioning of an underlying base table is altered. See ALTER TABLE (Basic Table Parameters).

View definitions that include the system-derived PARTITION column or the system-derived PARTITION#L n columns are appropriate for DBAs.