Generate and execute dynamic GRANT statements via. Teradata stored procedure

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Generate and execute dynamic GRANT statements via. Teradata stored procedure

I want to generate dynamic GRANT stmts in a stored procedure, it this even possible? When I execute a simple pre-defined GRANT stmt with a proc. it executes fine, however when I call it by grnerating it dynamically I get the below error:

 

CALL Failed. 7683:  CURSOR_SAMPLE:Invalid statement specified inside a dynamic declare cursor/SQL statement. 

 

Here is how my code currently looks:

REPLACE PROCEDURE CERT_TOOLS_DB.CURSOR_SAMPLE  ()BEGIN
DECLARE c, n INTEGER DEFAULT 0;
DECLARE ROLENAME VARCHAR(200);
DECLARE MYSQL VARCHAR(1200);
DECLARE cursor_name CURSOR FOR SELECT * FROM CERT_TOOLS_DB.ACCESSGRANT;

INSERT INTO CERT_TOOLS_DB.ACCESSGRANT
SELECT DISTINCT  'GRANT '|| TRIM(ROLE_NM) || ' TO "959375" ;' AS  GrantAccessOnPROD
 FROM CERT_METADATA_DB.SHRPNT_SUBJCT_AREA_ROLE  
WHERE SUBJCT_AREA_NM =  'Aadvantage Transaction and Customer Detail';

OPEN cursor_name;

label1:

LOOP
FETCH  cursor_name INTO ROLENAME;
IF (SQLSTATE = '02000') THEN
  LEAVE label1;
END IF;

SET MYSQL = ROLENAME;

BEGIN
DECLARE ROLEVAL SCROLL CURSOR WITHOUT RETURN FOR  SQL_STATEMENT ;
PREPARE SQL_STATEMENT FROM MYSQL;
OPEN ROLEVAL;
END;

--FETCH NEXT FROM cursor_name;
END LOOP label1;
CLOSE cursor_name;

END;

CALL CERT_TOOLS_DB.CURSOR_SAMPLE ();

 Thanks in Advance!

 


Accepted Solutions
Teradata Employee

Re: Generate and execute dynamic GRANT statements via. Teradata stored procedure

Roles (with or without ADMIN permission) can only be granted to Users, not Databases.

 

You need to specify SQL SECURITY CREATOR in your CREATE/REPLACE PROCEDURE, so that the stored procedure will depend on permissions of SYSDBA (for example) and not CERT_TOOLS_DB.

1 ACCEPTED SOLUTION
4 REPLIES
Highlighted
Junior Contributor

Re: Generate and execute dynamic GRANT statements via. Teradata stored procedure

Cursors are for Select statements, but you're trying to open a GRANT.

 

You simply need to EXECUTE IMMEDIATE your Grant.

And a FOR cursor simplifies it, no need for OPEN/LOOP/FETCH:

 

FOR cursor_name AS 
   SELECT DISTINCT  'GRANT '|| Trim(ROLE_NM) || ' TO "959375" ;' AS  GrantAccessOnPROD
   FROM CERT_METADATA_DB.SHRPNT_SUBJCT_AREA_ROLE  
   WHERE SUBJCT_AREA_NM =  'Aadvantage Transaction and Customer Detail'
DO
   SET MYSQL = cursor_name.GrantAccessOnPROD;
   EXECUTE IMMEDIATE MYSQL;
END FOR;
Teradata Employee

Re: Generate and execute dynamic GRANT statements via. Teradata stored procedure

Thanks for the reply. Now I am getting the below error message when I call the procedure:

CALL CERT_TOOLS_DB.CURSOR_SAMPLE ();

CALL Failed. 5616:  CURSOR_SAMPLE:The user is not authorized to grant or revoke role 'CERT_LYLTY_METRICS_USR_RX'. 

 

What grant am I missing? I ran 

GRANT "CERT_LYLTY_METRICS_USR_RX" TO CERT_TOOLS_DB WITH ADMIN OPTION;

But it gave me error : GRANT Failed. 5617:  Grantee is not a valid user or role.  

 

I am logged on as SYSDBA and have all the permission to execute this grant but I think I am missing an intermediate grant here since its the procedure that will be granting access and not me.

 

Please help!

Teradata Employee

Re: Generate and execute dynamic GRANT statements via. Teradata stored procedure

Roles (with or without ADMIN permission) can only be granted to Users, not Databases.

 

You need to specify SQL SECURITY CREATOR in your CREATE/REPLACE PROCEDURE, so that the stored procedure will depend on permissions of SYSDBA (for example) and not CERT_TOOLS_DB.

Teradata Employee

Re: Generate and execute dynamic GRANT statements via. Teradata stored procedure

Worked like a charm!
Thanks, dnoeth & Fred !