Transfer Ownership with GIVE Statement | Teradata Vantage - 17.00 - 17.05 - Transferring Ownership with GIVE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
ujp1472240543947

Transfer databases and users from one immediate owner to another using the GIVE statement, for example:

GIVE Finance TO Alice;

The GIVE statement also transfers all child databases and users as well as the objects (tables, views, macros, indexes, stored procedures, and triggers) owned by the transferred object.

Rules affecting transfer of ownership include:

  • You cannot give an object to children of the object.
  • The permanent space owned by the database or user is also transferred.
  • To use GIVE, you must have the explicit DROP DATABASE/USER privilege on the object being transferred and explicit DROP/CREATE DATABASE/USER privilege on the receiving object.
  • Any explicit privileges granted to others on a transferred user are not automatically revoked. You need to explicitly REVOKE explicit privileges.
  • Changing ownership hierarchy impacts implicit privileges. When a database or user has a new owner, the former owner loses implicit privileges on the object (and all objects below it in the hierarchy). The new owner (and those above him in the hierarchy) gains implicit privileges on the object and all objects below it in the hierarchy.

Transferring ownership affects space allocation. Plan carefully and check space distribution and object ownership before using the GIVE statement.