DROP USER Usage Notes - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Effects of Dropping a User on Database Privileges

When you drop a user, the system deletes from dictionary tables individual privileges granted directly to the dropped user and privileges granted through role assignment.

Dropping database objects that exist in the user space before the DROP USER statement (required) removes any privileges on the objects from roles and other users or databases.

Any privileges granted by the dropped user on objects outside the user space remain in the system.

Effects of Dropping a User on User-Created Roles and Profiles

Roles and profiles created by a dropped user remain in the system after the user is dropped.

Journal Tables

If a user contains a journal table in its space, then the user cannot be dropped until the journal table is removed from the system. A MODIFY USER statement must be used to drop the journal table.

Join Indexes

If a user contains a table referenced by a join index contained in a different database or user, then you cannot drop the user that contains the referenced table unless you first drop the referencing join index.

See DROP JOIN INDEX for more information.

Triggers

If a user contains a table referenced by a trigger contained within a different database or user, then you cannot drop the user that contains the referenced table unless you first drop the referencing trigger.

For more information, see DROP TRIGGER.

Query Bands

You cannot drop a user who is logged on as a trusted or permanent user through a proxy connection.

When you drop a user who is a trusted or permanent proxy user, the system deletes the rows for that user in DBC.ConnectRulesTbl.

DBQL Rules

Before you can drop a user for whom one or more DBQL rules have been created, you must first remove those rules for that user. See DROP USER Examples.

Dropping a Populated User

You cannot drop a populated user. Complete the following procedure to make sure that a user is empty before you attempt to drop it.
  1. Drop any join indexes in another database or user that reference a table in the current user. See DROP JOIN INDEX.
  2. Drop any triggers in another database or user that reference a table in the current user. See DROP MACRO.
  3. Drop any journal tables in the current user. See MODIFY USER.
  4. Perform either of the following procedures:
  5. Drop the current user.

DROP Processes

The drop operation verifies that the user is empty, checks that the user does not own any other databases or users, drops the user, and adds the PERM and TEMPORARY space that the drop makes available to that of the immediate owner database or user.

You must restore a dropped user before you can recover it using the Dump and Restore utility.

DROP USER and Locks

When a DROP USER statement is processed, the system places an EXCLUSIVE lock on the user being dropped.

Among the dictionary locks required for dropping the user, WRITE locks for deleting privileges from the partitioned DBC.AccessRights table are placed as follows:
  • An all-AMP WRITE lock is placed on the partition range assigned to objects in the dropped user/database. Although DROP USER can run only after all objects in the user have been dropped, a final delete is done to clean up any leftover privileges on the user's dropped objects.
  • For DROP USER only, an optional single-AMP WRITE lock is placed on the user RowHash in all partitions if the user has been granted explicit privileges on objects in other users or databases that need to be deleted.

Drop Processes and Java External Procedures

After a DROP USER request has been processed successfully, and before returning its status to the client application, the UDF symbol cache must be spoiled. This is done as part of the dictionary cache spoiling process. Because you must successfully submit a DELETE USER request before you can submit the DROP USER request, these actions have already occurred. See DELETE USER.

With the DROP USER operation, the following actions are taken on the appropriate dictionary tables:
  1. The system checks DBC.Jar_Jar_Usage to determine if any JARs in the user to be deleted are in the SQL-Java path of another JAR.

    If so, the request aborts and the system returns an error to the requestor.

  2. Delete all rows in DBC.Jar_Jar_Usage where the value for the JarDatabaseId column matches the ID of the user to be deleted.
  3. Delete all rows in DBC.Routine_Jar_Usage where the value for the DatabaseId column matches the ID of the user to be deleted.
  4. Delete all rows in DBC.Jars where the value for the DatabaseId matches the ID of the user to be deleted.

Referenced Objects

You cannot drop a user in which a join index is defined without first dropping the index.

You cannot drop a user that contains a table on which a join index is defined without first dropping the index.