Stored Procedure vs Macro

Database
Junior Supporter

Stored Procedure vs Macro

Assuming that I have only DML statements that I plan to place in ETL with a input date parameter then what is the advantage of using Stored Procedure over Macro and vice versa?

According to me if the Stored Procedure is created using SQL SECURITY OWNER than both are at par.

One difference is in the EXPLAIN where in for the Macro you will get execution plan for all statements inside the Macro while for Stored Procedure you don’t get any details.
9 REPLIES 9
Ambassador

Re: Stored Procedure vs Macro

Hi,

 

In a macro all DML statements are treated as a single request (and therefore) part of a single transaction. If one fails they all fail. In a SP you can code around this.

 

A macro can be created in any transaction mode and executed in any transaction mode. The SP must be called in the same transaction mode that it was created in.

 

IMHO a macro is a lot easier to code than a SP.

 

Cheers

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Supporter

Re: Stored Procedure vs Macro

Hi Dave,

Can you elaborate more on the transaction mode difference?

Also if I create a SP with SQL SECURITY OWNER in DB_A and call it in another SP in DB_B, then when I call DB_B SP will the first SP be executed with access rights of DB_A or DB_B?
Teradata Employee

Re: Stored Procedure vs Macro

If you CREATE (or replace) a SQL Stored Procedure in Teradata mode, that procedure can only be called from sessions using Teradata mode. If you Create the SP in ANSI mode, then it can only be called from sessions using ANSI mode. Error handling and transaction semantics are different depending on the mode - see the SQL Reference - Stored Procedures and Embedded SQL manual.

 

With SQL SECURITY OWNER, SP defined in A will check DB_A's permissions regardless of how it is invoked. Note that the special CREATE OWNER PROCEDURE right is required to create a procedure that uses SQL SECURITY OWNER. This is also explained in the same manual.

Junior Supporter

Re: Stored Procedure vs Macro

Hi Fred,

 

One more question. I have an existing SP A created with SQL SECURITY CREATOR and this SP is called in another SP B in ETL. I am thinking of replacing SP A with SQL SECURITY OWNER so that it is freed from a particular User and only depends on the DB. Will this change have an effect on the call of SP B in ETL?

 

PS It would have been nice if there was a SQL SECURITY option for the Role of the creator and not the creator itself. And during compilation and execution the rights of the Role is checked and not the user.

Teradata Employee

Re: Stored Procedure vs Macro

Changing the SQL SECURITY clause for the called procedure will have no effect on the calling procedure.

 

Your comment brings up an interesting technical point. A role is effective in connection with a session for a user, and may or may not be "active" for any given session. This may not be obvious, since many sites will set ALL granted roles being active as the default for most users.

 

As it relates to Stored Procedures, when Creator rights are being checked at CREATE/REPLACE time, then rights from a role active in that session will be considered. But if Creator rights are being checked at execution time, only rights granted directly to the user are in effect.  

Junior Supporter

Re: Stored Procedure vs Macro

Hi Fred,

How can I check using queries what is the setting at my site for “many sites will set ALL granted roles being active as the default for most users"?

Also we have a situation at our site. At inception all business users and the ETL user had rights explicitly granted without roles. Stored procedures were created by business users and the ETL user would call the SP using scheduling tools. Business users could modify the SP without informing the ETL user and things ran smoothly.

Later there was a IT policy change to LDAP and all business users were given new roles based user id and rights and the ETL user was left untouched. Post this if a business user modifies a SP then ETL fails the next day, unless the modified SP is recreated by the ETL user.

My question is this situation related or due to your point on difference in Creater right checks on Stored Procedures during creation and execution?
Teradata Employee

Re: Stored Procedure vs Macro

DBC.UsersV view (and DBC.DBase table) have a RoleName column that names the default role (or the string 'All')  if you are using database authorization.

If you are using directory-based authorization (external roles mapped at logon), you'd need to check in the directory.

 

It certainly sounds as if CREATOR security could be the problem in this situation. You might consider simply using INVOKER security; then execution of the SP would depend on the ETL user's permissions.

 

Highlighted
Ambassador

Re: Stored Procedure vs Macro

And if the ALL role is not set by default most end users will enable it manually as soon as they notice it's possible :-)

You can query dbc.SessionInfoV.CurrentRole to see the roles of the currently logged on sessions.

Junior Supporter

Re: Stored Procedure vs Macro

As per your advice I think both SQL SECURITY CREATOR and SQL SECURITY DEFINER will be causing this problem. I think SQL SECURITY INVOKER or SQL SECURITY OWNER should be used.

Thank You Fred, Dave, Dnoeth for all your advice.

It will take me some time to try and get back to you all.