DDL in a macro

Database
Enthusiast

DDL in a macro

       I created a macro as below by using log-in "cly_dba",

  replace macro cly_db.zam_emp_mac1

 as

 (

   create table cly_db.zam_dummy

 (

 col1 integer,

 col2 varchar(10)

 );

 );

But when i execute above macro, it is giving me the error written below,

exec   cly_db.zam_emp_mac1 ;

""3524: An owner referenced by user does not have CREATE TABLE WITH GRANT OPTION access to database cly_db"".

I tried granting rights to database "cly_db" to user "cly_dba" as below,

grant all on cly_db to cly_dba with grant option ;

grant create table on cly_db to cly_dba with grant option ;

But still I am getting above error.

And when I create the same table directly in SQL assistant as below, it  is working fine.

 create table cly_db.zam_dummy

 (

 col1 integer,

 col2 varchar(10)

 );

Please let me know why it is giving error when i try to create table through macro.

2 REPLIES
Teradata Employee

Re: DDL in a macro

Hi,

you can try to do the following:


grant create table on cly_db to cly_db with grant option;

The idea is that the privilege to do things inside the macro should be granted to the user/database where the macro resides, not to the user who executes the macro (provided that the user has execute privilege to execute the macro).

Regards,

Vlad.

Enthusiast

Re: DDL in a macro

Thank you Vlad. I did as you suggested and its working fine.