17.10 - Altering or Dropping CONSTRAINT Objects - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Security Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Security
Publication ID
B035-1100-171K
Language
English (United States)

The process for altering or dropping a CONSTRAINT object requires that you first remove all constraint assignments.

Modifying the name:value pairs in a CONSTRAINT object after a table that contains a corresponding constraint column is archived can have unintended effects on COPY and RESTORE operations. Such modifications may compromise security, depending on which parameters are changed. See Archive, Copy, and Restore Requirements.
  1. Determine the database objects to which a security constraint is assigned.
    1. Find the tables and indexes that have the security constraint. See Finding Tables and Indexes with a Security Constraint.
    2. Find views that have the security constraint. See Finding Views that Include a Security Constraint.
  2. DROP the indexes identified in step 1a.
  3. Remove the constraint columns from all tables identified in step 1a using the ALTER TABLE statement. For example:
    ALTER TABLE  table_name 
       DROP  constraint_column_name  ;
  4. REPLACE all views identified in step 1b to remove the constraint column.
  5. Identify the users and profiles to which the constraint is assigned. See Finding Users or Profiles with an Assigned Constraint.
  6. Remove the security CONSTRAINT object assignments from:
  7. After removing all assignments for a security constraint, you can either:
    • Use the ALTER CONSTRAINT statement to change the constraint object.
      If you do not specify one of the available options in the ALTER CONSTRAINT statement, the CONSTRAINT retains the existing values for the option.
    • Use the DROP CONSTRAINT statement to drop the constraint object.

    For information about the ALTER CONSTRAINT and DROP CONSTRAINT statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

  8. After altering or dropping a constraint, reverse the actions done in steps 2 through 6 to reapply security constraints to tables, views, users and profiles.