Rules for privileges Keywords - Advanced SQL Engine - Teradata Database

The following rules apply to using the privileges for the REVOKE (SQL Form) statement:

• You can specify any combination of privileges appropriate for the corresponding database objects. However, the user submitting the statement must have those privileges, either implicitly or explicitly and WITH GRANT OPTION, on all of the specified objects.
• The CHECKPOINT privilege applies to performing both the SQL statement and the Host Utilities (HUT) Archive/Recovery commands DUMP and RESTORE.

  The DUMP and RESTORE privileges refer to the corresponding HUT command performed on the specified object.

  RESTORE also refers to execution of the following HUT commands:

  • ROLLBACK
  • ROLLFORWARD
  • DELETE JOURNAL

  If you specify CHECKPOINT, then the system revokes the privilege for the SQL statement and for the Archive/Recovery utility commands DUMP and RESTORE.

  The DUMP and RESTORE privileges refer individually to the corresponding HUT commands performed on the specified object.

• CREATE DATABASE, FUNCTION, MACRO, PROCEDURE, TABLE, VIEW, or USER, and DROP DATABASE or USER are allowed only on databases or users.
• DROP TABLE includes ALTER TABLE.
• DROP MACRO, DROP PROCEDURE, or DROP VIEW include REPLACE MACRO, REPLACE PROCEDURE, or REPLACE VIEW, respectively.
• Only DROP and EXECUTE are allowed on specified procedures, UDFs, function mappings, or macros.

  If the object is a UDF or procedure, then you must precede its name with FUNCTION or PROCEDURE, as appropriate.

  If you do not specify one of those keywords, then the system assumes that the specified object name references a macro. If a macro with that name does not exist, the statement returns an error.

• Only ALTER FUNCTION, CREATE FUNCTION, DROP FUNCTION, EXECUTE FUNCTION, and FUNCTION are allowed on external UDF.
• ALTER FUNCTION is not a valid privilege for SQL UDFs.
• Only ALTER PROCEDURE, DROP PROCEDURE, and EXECUTE PROCEDURE are allowed on SQL procedures.
• Only ALTER EXTERNAL PROCEDURE, DROP PROCEDURE, and EXECUTE PROCEDURE are allowed on an external procedure.
• DUMP, RESTORE, and CHECKPOINT are not allowed on views.
• If you revoke RESTORE, then the privilege to perform the following utility commands is also revoked:
  • ROLLBACK
  • ROLLFORWARD
  • DELETE JOURNAL.
• DATABASE, FUNCTION, JOIN INDEX, MACRO, PROCEDURE, PROFILE, ROLE, TABLE, VIEW, and USER confer both CREATE and DROP privileges on the respective database objects.

  If the DATABASE, FUNCTION, GLOP, JOIN INDEX, MACRO, PROCEDURE, PROFILE, ROLE, TABLE, USER, VIEW, or ZONE keyword is specified without CREATE or DROP, then CREATE and DROP are revoked.

• ANSI/ISO SQL:2011 supports the following privileges only:
  • DELETE
  • EXECUTE
  • INSERT
  • REFERENCES
  • SELECT
  • TRIGGER
  • UPDATE

    The other privileges are extensions to the ANSI/ISO SQL:2011 standard.

• In most cases, the privilege keyword agrees with the keyword of a Teradata SQL statement. However, the following operations privileges do not correspond to any SQL statements:
  • DATABASE
  • DUMP
  • MACRO
  • PROCEDURE
  • RESTORE
  • TABLE
  • USER
  • VIEW
• INSERT, REFERENCES, SELECT, and UPDATE have both table- and column-level options.
• You can revoke CREATE PROFILE, DROP PROFILE, CREATE ROLE, DROP ROLE, CREATE ZONE, and DROP ZONE privileges from users only, not from roles or databases.
• CREATE MAP, DROP MAP, CREATE PROFILE, DROP PROFILE, CREATE ROLE, DROP ROLE, CREATE ZONE, and DROP ZONE are system privileges. You can revoke the privileges from a user, but not on a specific object.
• The STATISTICS privilege enables collecting and dropping statistics.