16.20 - Case Study: Granting INSERT and DELETE on a View - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Control Language

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1149-162K

Assume that the view Bobby.ViewB references the view Allen.ViewA, which in turn references Allen.BaseTable. When Bobby enters the following statement, the system checks that the privileges in the table following the statement exist.

GRANT INSERT, DELETE
ON Bobby.ViewB TO Chuck;

The following stages explain this process:

  1. Bobby has INSERT WITH GRANT OPTION on Bobby.ViewB or on himself.
  2. Bobby has DELETE WITH GRANT OPTION on Bobby.ViewB or on himself.

Unless revoked, Bobby has these privileges on Bobby.ViewB explicitly because they were granted to him automatically when he created the view.

Bobby also had these privileges on himself because they were granted explicitly when user Bobby was created.

Because Bobby owns Bobby.ViewB, he also has these privileges implicitly.

Implicit privileges cannot be revoked. For any other type of privilege, however, the system might not find one or more of the necessary privileges. In this case, the system returns an error message to the user submitting the GRANT statement and the statement is not performed.

The breakdown of privilege types for this example is as follows:

Privilege Category THIS individual … HAS this privilege … ON this object …
Automatic Bobby INSERT WITH GRANT OPTION Bobby.ViewB
DELETE WITH GRANT OPTION
Allen INSERT WITH GRANT OPTION Allen.BaseTable
DELETE WITH GRANT OPTION
Explicit Bobby INSERT WITH GRANT OPTION Allen.ViewA
DELETE WITH GRANT OPTION