15.00 - GRANT (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

GRANT (Temporal Form)

Purpose  

Assigns the NONTEMPORAL and other explicit privileges on a database, user, table, or view to a user or group of users.

Syntax  

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

 

Syntax Element …

Specifies …

ALL [PRIVILEGES]

that the specified user is to receive all privileges that can be granted on the specified object.

GRANT ALL includes the NONTEMPORAL privilege if the InclNTforGrntOrRevokAll field of the DBS Control utility is set to TRUE. Otherwise, GRANT ALL excludes the NONTEMPORAL privilege.

All of the privileges, including the NONTEMPORAL privilege, that the grantor has on the object are granted if a user has them with WITH GRANT OPTION.

NONTEMPORAL

that the user can use the NONTEMPORAL prefix to perform nontemporal operations on transaction-time and bitemporal tables contained in database_name or user_name, on table_name, or on view_name.

privilege

a privilege other than NONTEMPORAL.

For details, see GRANT in SQL Data Control Language.

ALL BUT

that the named user is to receive all privileges that can be granted on the specified object except for those specified in the privilege list. As in ALL, only those object privileges owned by the grantor WITH GRANT OPTION are granted.

ON database_name

the name of a database that contains or may contain transaction-time or bitemporal tables or both.

ON user_name

the name of a user that contains or may contain transaction-time tables, bitemporal tables, or both.

ON table_name

the name of a transaction-time or bitemporal table.

ON view_name

the name of an updatable view created on a transaction-time or bitemporal table.

TO [ALL] username

the name of an existing database or user that identifies the recipient.

If you specify ALL, then the object privileges are granted to the named database or user and to every database or user owned by that database or user now and in the future.

WITH GRANT OPTION

that the grantee receives the granted privileges WITH GRANT OPTION.

NONTEMPORAL Privilege

The NONTEMPORAL privilege is required to use the NONTEMPORAL prefix with ALTER TABLE, CREATE TABLE AS, DELETE, INSERT, and UPDATE statements.

These nontemporal operations allow modifications to closed rows, which are normally prohibited on tables that have transaction time. For example, they allow direct modification of the transaction time column values, even for rows that are closed in transaction time. They also allow physical deletion of rows that are closed in transaction time. Closed rows are normally inaccessible to modifications. They provide a history of prior row modifications and deletions, and are saved indefinitely in the table.

Because nontemporal operations can be used to circumvent the normal processing of tables with transaction time, the use of nontemporal operations is discouraged. However, nontemporal operations may be required for certain kinds of database maintenance, as when very old history rows must be archived and deleted for space considerations, and for correcting problems or corruptions in temporal tables.

The availability of nontemporal operations at the system level is controlled by the NONTEMPORAL privilege and by the EnabNonTempoOp setting in DBS Control. By default, nontemporal operations are not allowed, regardless of the NONTEMPORAL privilege. To enable nontemporal operations for users with the NONTEMPORAL privilege, use the DBS Control utility. For details, see Utilities.

Usage Notes

A NONTEMPORAL privilege is a table and view level privilege and can also be granted at the database or user level. This privilege is not an automatic privilege and must be explicitly granted by user DBC or another user who has sufficient privileges.

The NONTEMPORAL privilege is granted implicitly to DBC with GRANT OPTION. The system does not grant the NONTEMPORAL privilege implicitly to any other users or databases.

The NONTEMPORAL privilege follows the system convention that it can be granted by an owner on any owned objects. Note that an owner must explicitly grant the NONTEMPORAL privilege to itself in order to use the NONTEMPORAL qualifier on owned objects.

For example, assume user john123 has created a table, MyTemporalTable, and is logged in to the database. In order to perform nontemporal operations to this table, john123 would first need to send the following request:

GRANT NONTEMPORAL ON mytemporaltable TO john123;

The privilege is recorded as 'NT' in the AccessRight column in the DBC.AccessRights table.

Any use of the NONTEMPORAL privilege, whether successful or denied by the system, is automatically logged in the access logging tables. Nontemporal logging requires no explicit BEGIN LOGGING statement, and cannot be disabled.

For statements that include the NONTEMPORAL prefix, the system checks the NONTEMPORAL privilege in addition to those that the statement normally requires. For example, to execute NONTEMPORAL DELETE requires both NONTEMPORAL and DELETE privileges.

Related Information

 

For more information on...

See...

GRANT (regular form)

SQL Data Control Language

REVOKE (temporal form)

“REVOKE (Temporal Form)” on page 183

DBS Control utility

Utilities

DELETE (temporal form)

“DELETE (Temporal Form)” on page 113

INSERT (temporal form)

“INSERT/INSERT … SELECT (Temporal Forms)” on page 119

Nontemporal operations

“Nontemporal Operations” on page 25

UPDATE (temporal form)

“UPDATE (Temporal Form)” on page 164