CREATE/REPLACE MACRO Syntax | Teradata Vantage - CREATE MACRO and REPLACE MACRO - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Defines a set of statements that are frequently used or that perform a complex operation. The statements in the macro body are submitted when the macro is invoked by a subsequent EXECUTE statement.

REPLACE MACRO redefines an existing macro. If the specified macro does not exist, REPLACE MACRO creates a new macro with that name.

Required Privileges for CREATE MACRO

You must have the CREATE MACRO privilege on the containing database or user in which the macro is to be created. The creator of a macro is automatically granted the DROP MACRO and EXECUTE privileges WITH GRANT OPTION on the macro.

The user creating a macro must have the privileges for all statements it performs.

Once a macro has been created, its immediate owner is the database or user in which it is contained, not the user who created it. The immediately owning database or user must have all the appropriate privileges for executing the macro, including WITH GRANT OPTION.

Access to data via a macro is controlled by the privileges of its immediate owner, not by the privileges of its creator. This can be a security issue. See “Security Considerations With CREATE MACRO Privilege” under “GRANT (SQL Form)” in Teradata Vantage™ - SQL Data Control Language, B035-1149 for details.

The user who performs a macro need not be aware of the tables affected by its performance.

Required Privileges for REPLACE MACRO

You must have the DROP MACRO privilege on an existing macro or its containing database or user to replace it.

You must have the CREATE MACRO privilege on the macro or its containing database or user if it does not already exist.

The user replacing a macro must have the privileges for all statements it performs.

Once a macro has been replaced, its immediate owner is the database in which it exists, not the user who replaced it. The immediately owning database must have all the appropriate privileges for executing the macro, including WITH GRANT OPTION.

Privileges Granted Automatically

The following privileges are granted automatically to the creator of a macro:
  • DROP MACRO
  • EXECUTE
  • GRANT