Security Considerations With the CREATE MACRO Privilege | Teradata Vantage - Security Considerations With the CREATE MACRO Privilege - Advanced SQL Engine - Teradata Database

Before you grant CREATE MACRO on a database or user, it is extremely important to realize that the recipient of that privilege can create and perform macros that have all the privileges of that database or user. This is because for CREATE MACRO, the privileges are inherited from the immediate owner of the macro, not from its creator.

As a result, the grantee can create macros that contain DCL and DDL statements that are not checked for the privileges of the creator. This means that you are implicitly assigning privileges to the macro creator that they have no explicit, implicit, or automatic privilege to perform. This might not be a desirable result and you should be exceedingly careful when granting this privilege.

For example, consider the scenario presented in the following graphic:

The compensation database is owned by user DBA.

User SysAdminDBA, the system administrator for compensation, has privileges on compensation, including CREATE MACRO WITH GRANT OPTION, and on all objects owned by compensation.

SysAdminDBA creates user CompAnalyst6 for an entry level programmer who has been assigned to produce compensation reports for several routine audits performed by various state and federal regulatory agencies. To ensure that CompAnalyst6 does not have access to critical private employee base salary and bonus information, she has been granted only a restricted set of privileges on objects in the compensation database.

To make it easier for CompAnalyst6 to create the reports, SysAdminDBA also grants her the CREATE MACRO privilege on compensation as follows:

GRANT CREATE MACRO
ON compensation
TO companalyst6;

Because the privileges for executing macros in compensation derive from compensation, CompAnalyst6 can create and perform macros that report on just the sort of private data she was meant to be restricted from viewing.

For example, CompAnalyst6 can grant herself full access to all tables in the database through a simple macro and then create any database object or perform a query that reports on salary and bonus data for each employee in the enterprise in the three quick steps outlined in the following procedure:

1. CREATE MACRO everything AS

    (GRANT ALL PRIVILEGES ON compensation

    TO companalyst6;);

2. EXECUTE everything;
3. SELECT *

   FROM salary, bonus;

CompAnalyst6 can also modify data and drop tables in the compensation database.