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.

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.)

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 inthe CREATE/REPLACE PROCEDURE statement. When the stored procedure is compiled orexecuted, Teradata Database checks for the required privileges based on the following optionsof 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'.

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.)