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

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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.