Download (direct link):
Transfer of Ownership
You can transfer databases and users from one immediate owner to another. The GIVE statement transfers a database or user permanent space to a recipient you specify. The GIVE statement also transfers all child databases and users as well as the objects (tables, views, macros, indexes, stored procedures, triggers) owned by the transferred object.
Note: Transfer of ownership affects space ownership and privileges. Rules affecting transfer of ownership include:
• You can transfer only databases and users with the GIVE statement. (GIVE does not operate at the table level.)
• You cannot give an object to one of its children.
• All of the objects owned by the database or user are also transferred.
• The permanent space owned by the database or user is also transferred.
• You must have the DROP DATABASE privilege on the object being transferred and CREATE DATABASE privilege on the receiving object.
• Any privileges granted to others by a transferred user are not automatically revoked, even if the transferred user is subsequently dropped. You need to explicitly REVOKE granted privileges
Removing a Hierarchy Level
Assume that User A is the owner of Users B, C, and D.
Teradata RDBMS Database Administration
1 - 17Chapter 1: Database Hierarchy
Changing the Hierarchy with GIVE
Now User A no longer needs User B, but wants to keep Users C and D. To remove User B from the hierarchy, User A must perform the following:
1 Transfer ownership of User C to User A: GIVE C TO A The GIVE statement transfers both users C and D because the GIVE statement transfers the named user and all of its descendants in the hierarchy. Note that the privileges of User C are not altered. The GIVE statement does not change privileges defined in DBC.AccessRights.
2 Delete all tables/views/macros/indexes/triggers/stored procedures from User B. All privileges on these tables/views/macros/indexes / triggers/stored procedures are removed.
3 Drop User B. User A recovers the permanent space held by User B. It also removes journals (if any). Note that the journal cannot be dropped until all users of the journal are removed.
Now the hierarchy is:
Note: The privileges for Users C and D remain intact. Although User B no longer exists, privileges granted by User B are not automatically revoked. User A must use the REVOKE statement to change the privileges of Users C and D.
For the implications of how privileges are affected by GIVE, see:
• "Transferring Privileges" on page 6-16.
• GIVE Statement under "Data Control Language Syntax" in Teradata SQL Reference, Volume 4.
• "Controlling Data Access" in Teradata RDBMS Security Administration.
1 - 18
Teradata RDBMS Database Administration 1 - 17Chapter 1: Database Hierarchy
You cannot drop a database or user when any object exists in that space. You use DELETE to remove the objects, and DROP to release the space. This section provides an overview of using the DELETE and DROP statements.
Use DELETE DATABASE and DELETE USER statements to delete all tables, views, macros, stored procedures and triggers from a database or user. You must have DROP DATABASE or DROP USER privilege on the referenced database or user to delete objects from them.
The database or user remains in the Teradata RDBMS as a named object. All space used by the deleted objects becomes available as spool space until it is reused as perm space. (To remove an empty database or user, use DROP.)
Note: A DELETE DATABASE or DELETE USER statement cannot be executed if the table references a join or hash index (according to the join/hash index rule in regard to DELETE and DROP).
Join, hash, and secondary indexes are not dropped by DELETE DATABASE or DELETE USER. A join or hash index must be dropped using a DROP JOIN/HASH INDEX statement. A secondary index is dropped using the DROP INDEX statement.
Use the DROP DATABASE or DROP USER statement to drop an empty database or user. Use DROP ROLE or DROP PROFILE to remove obsolete roles or profiles.
The database or user that you are dropping cannot own other databases or users, or entities such as data tables, indexes, journals, macros, views, stored procedures, and so forth.
For more information on how to delete or drop databases and users, see "Dropping an Empty Database or User" on page 2-3. For more information on roles and profiles, see "Implementing Roles and Profiles" on page 5-12.
Teradata RDBMS Database Administration
1 - 19Chapter 1: Database Hierarchy
1 - 20
Teradata RDBMS Database Administration 1 - 19Chapter 2:
Building the Teradata RDBMS
As data needs change dynamically over time, you can restructure databases to accommodate them. The individual user also can control owned data to meet changing requirements.
This chapter describes objects that comprise the Teradata RDBMS, including