Dynamic DCL statement execution

Database
Enthusiast

Dynamic DCL statement execution

Hi ,

I am trying to create a procedure to grant role to the users based on the user id inputted.

but it is not working .

Kindly do help me in debugging the same

Teradata Version - 13.10

Procedure code -

REPLACE  PROCEDURE RL(

IN UID VARCHAR(10))

BEGIN

    GRANT "VWSROLE","RPTROLE" TO UID ;

END;

I am new to writing SP for Teradata , do guide me on the same

regards,

Subramanian kaushik Gurumoorthy

4 REPLIES
Enthusiast

Re: Dynamic DCL statement execution

GS,

This shold get you started.

REPLACE PROCEDURE UADMIN.SP_CHG_ROLE ( 
IN IUSERNAME CHAR(30) ,
IN IROLENAME CHAR(30))
MAIN: BEGIN
DECLARE VCMD VARCHAR(4096) DEFAULT ' ';

SET VCMD ='GRANT '||TRIM(IROLENAME)||' TO '||TRIM(IUSERNAME)||';';
CALL DBC.SYSEXECSQL(:VCMD);

SET VCMD ='MODIFY USER '||TRIM(IUSERNAME)||' AS
DEFAULT ROLE = '
||TRIM(IROLENAME)||';';
CALL DBC.SYSEXECSQL(:VCMD);
END MAIN;

Rglass

Enthusiast

Re: Dynamic DCL statement execution

Hi Glass,

Thanks a lot for your reply.

I tried Implementing the same.

But getting the errror.

CALL Failed. 5616:  RLBASIC :The user is not authorized to grant or revoke role.

regards,

Subramanian kaushik Gurumoorthy

Enthusiast

Re: Dynamic DCL statement execution

GS,

Grant the role to UADMIN with the WITH ADMIN OPTION before executing the stored procedure. This in turn enable UADMIN to grant, revoke, or drop the role to other users

http://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/Database_Management/B035_1093_109...

Thanks!!

Enthusiast

Re: Dynamic DCL statement execution

Hi Velunatrajan

Thanks a lot.

That worked, after I tried creating the procedure within the user to whom access has been provided.

regards,

Subramanian kaushik Gurumoorthy