Macro vs Procedure - SQL SECURITY

Database
Enthusiast

Macro vs Procedure - SQL SECURITY

Does CREATE MACRO have an equivalent of CREATE PROCEDURE's SQL SECURITY clause? Or should the database of the user executing the macro always always have SELECT/UPDATE access, as needed, to the tables used in the MACRO?
Tags (2)
5 REPLIES
Junior Contributor

Re: Macro vs Procedure - SQL SECURITY

Q1: No.

Q2: The database where the Macro is stored must have the necessary rights WITH GRANT OPTION.

Teradata Employee

Re: Macro vs Procedure - SQL SECURITY

As Dieter alluded to, macro security works very differently from stored procedure security.

The answers to your questions can be found in the Teradata Database Reference "SQL Data Definition Language Syntax and Examples" / Chapter 8 "Macro Statements" / section "CREATE MACRO and REPLACE MACRO" / subsection "Required Privileges for CREATE MACRO".

Quoting:

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 SQL Data Control Language, B035-1149 for details.

Enthusiast

Re: Macro vs Procedure - SQL SECURITY

Thank you Dieter and Tom. I am a bit confused though.

As per the DDL document as quoted above :
The user creating a macro must have the privileges for all statements it performs.

And as the DCL document :
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;

My Question now is :
Why should Step 1 execute successfully if CompAnalyst6 does not have access to GRANT ALL PRIVILEGES?

What am I missing?

Are these documents suggesting Procedures are more secure than Macros?
Teradata Employee

Re: Macro vs Procedure - SQL SECURITY

Teradata Database Reference / SQL Data Control Language / Chapter 2 "Statement Syntax / section "Grant (SQL Form) / subsection "Security Considerations With the CREATE MACRO Privilege" says the following:

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. 

The purpose of that documentation is not to say that "stored procedures are more secure than macros". Instead, that documentation is intended to inform you that the CREATE MACRO privilege is a very powerful privilege, and great care must be taken to ensure that privilege is only granted as needed.

Enthusiast

Re: Macro vs Procedure - SQL SECURITY

I finally understood what Tom was trying to explain me. I have broken it down for newbies like.

Basically using CREATE MACRO I can execute any query for which the database (in which the macro is created) has access even if I don’t have access. That is because immediate owner of a macro is the database in which it is created. As can be seen below, Statement 1 failed but Statement 2 and 3 executed successfully.

Statement 1:

grant select on DB_A.NOT_MY_TABLE to MY_USER_ID;

Result Message:

GRANT Failed.  [3523] The user does not have SELECT WITH GRANT OPTION access to DB_A.NOT_MY_TABLE.

Statement 2:

replace macro DB_A.TEST_SUPER_USER as (

         grant select on DB_A.NOT_MY_TABLE to MY_USER_ID;

);

Result Message:

REPLACE MACRO completed. 0 rows processed. Elapsed Time = 00:00:00

Statement 3:

exec DB_A.TEST_SUPER_USER;

Result Message:

EXECUTE completed. 0 rows processed. Elapsed Time = 00:00:00