Search code examples
db2data-warehouse

Is it possible to Grant Roles to Users in a Stored Procedure?

I am working on DB2 Data Warehouse. I am trying to write a stored procedure that involves Granting a input user a role.

CREATE OR REPLACE MYSCHEMA.PROCEDURE SP_CHECKIN_USER (
IN userid varchar(9),
IN groupid varchar(10)
) LANGUAGE SQL
BEGIN

-- do some work here
GRANT ROLE groupid TO USER userid;

END

When I try to run this I get the error:

"GROUPID" is undefined name .. SQLCODE=-204, SQLSTATE=42704, DRIVER=...
Run of routine failed.
Solution

  • Yes it is possible if you use dynamic SQL for the grant role inside the strored procedure.

    For example:

       declare v_sql varchar(1024);
   set v_sql = 'grant role '||trim(p_groupid)||' to user '||trim(p_userid)||' ';
   execute immediate v_sql;

    Remember that all other rules still apply, the role must pre-exist, the user must pre-exist, the authid must have all relevant permissions etc.