Can't grant role within a 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.
Enthusiast

Can't grant role within a stored procedure

hello,

I want to create a procedure that must grant role to user :

 

REPLACE PROCEDURE diag_n3.test_rbo_grant (usertdt VARCHAR(30), coad VARCHAR(1), CdRole VARCHAR(100) )
BEGIN
 DECLARE vGrantStmt VARCHAR(3000);
 SET vGrantStmt = 'GRANT ' || CdRole || ' TO ' || usertdt || ' ;' ;
 CALL DBC.SYSEXECSQL(vGrantStmt);
END;

 

But here's the result when I run it :

 

CALL diag_n3.test_rbo_grant('inubolr','9','G_INUBOLR');

 

CALL Failed. 5616:  TEST_RBO_GRANT:The USER IS NOT authorized TO GRANT OR REVOKE ROLE 'G_INUBOLR'.


Someone would have an idea what's missing privilege ?

 

 


Accepted Solutions
Teradata Employee

Re: Can't grant role within a stored procedure

As Dave says, check the manuals for "SQL SECURITY" options. There is also a table in the documentation for CREATE PROCEDURE (in the SQL DDL Syntax manual). Note that for DEFINER, both creator and owner privileges are checked (i.e. both must have the necessary access rights).

 

For this procedure, CREATOR security might be a better option. Or if the creator is an individual user that could potentially be dropped in the future, consider creating a "permanent" user to hold this sort of SP, and using OWNER security. (in which case the creator wold need to be granted the special CREATE OWNER PROCEDURE right first.)

1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: Can't grant role within a stored procedure

Hi,

 

With SP's you have a number of security options. These are listed as (from the "SQL Stored Procedures and Embedded SQL" manual):

 

You can specify how privilege checking is handled by defining the SQL SECURITY clause in
the CREATE/REPLACE PROCEDURE statement. When the stored procedure is compiled or
executed, Teradata Database checks for the required privileges based on the following options
of the SQL SECURITY clause:
• CREATOR
• DEFINER
• INVOKER
• OWNER

(If not specified then DEFINER is the default)

 

Currently, your SP is using the access rights of the username that issued the CREATE/REPLACE PROCEDURE statement. That username needs to be an 'admin' on role 'G_INUBOLR'.

 

HTH
Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Can't grant role within a stored procedure

As Dave says, check the manuals for "SQL SECURITY" options. There is also a table in the documentation for CREATE PROCEDURE (in the SQL DDL Syntax manual). Note that for DEFINER, both creator and owner privileges are checked (i.e. both must have the necessary access rights).

 

For this procedure, CREATOR security might be a better option. Or if the creator is an individual user that could potentially be dropped in the future, consider creating a "permanent" user to hold this sort of SP, and using OWNER security. (in which case the creator wold need to be granted the special CREATE OWNER PROCEDURE right first.)

Junior Supporter

Re: Can't grant role within a stored procedure

You can created this SP in database "sysdba". Since, sysdba is the master owner, it would have grant on the role.

Highlighted
Enthusiast

Re: Can't grant role within a stored procedure

thanks for all