How to run dynamic SQL using creator's authorizations
We can have 2 types of SQLs in a proc.
1) Static
2) Dynamic
One very important difference bw them is that while executing the procedure,for Static SQLs DB2 uses authorization ID of the package owner as the value to be used for authorization . But in dynamic SQL , DB2 uses authorization ID of the package executor as the value to be used for authorization.
We can override this behaviorfor dynamic SQLs by using dymanic rules bind.
Bind behavior
At run-time, DB2 uses all the rules that apply to static SQL for authorization and qualification.
That is, take the authorization ID of the package owner as the value to be used for authorization checking of dynamic SQL
statements and the package default qualifier for implicit qualification of unqualified object references within dynamic SQL statements.
At run-time, DB2 uses all the rules that apply to static SQL for authorization and qualification.
That is, take the authorization ID of the package owner as the value to be used for authorization checking of dynamic SQL
statements and the package default qualifier for implicit qualification of unqualified object references within dynamic SQL statements.
call set_routine_opts(get_routine_opts() || ' DYNAMICRULES BIND')@
DROP PROCEDURE SCHEMA.REFRESH_MQT@
CREATE PROCEDURE SCHEMA.REFRESH_MQT(IN IN_TABLE_NAME VARCHAR(128),
OUT o_sqlcode integer,
OUT o_sqlstate CHAR(5),
OUT o_MESSAGE VARCHAR(255))
LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 0
P1:BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT ' ';
DECLARE stmt VARCHAR(228) ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
EH: BEGIN
get diagnostics EXCEPTION 1 o_MESSAGE = MESSAGE_TEXT;
VALUES (SQLSTATE,SQLCODE) INTO o_sqlstate, o_sqlcode;
END EH;
SET stmt='SET INTEGRITY FOR SCHEMA.'||TRIM(IN_TABLE_NAME)||' IMMEDIATE CHECKED';
EXECUTE IMMEDIATE stmt;
SET stmt='REFRESH TABLE SCHEMA.R_'||TRIM(IN_TABLE_NAME);
EXECUTE IMMEDIATE stmt;
END P1
@
-- Restore the default bindrules
call set_routine_opts(NULL)@
OUT o_sqlcode integer,
OUT o_sqlstate CHAR(5),
OUT o_MESSAGE VARCHAR(255))
LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 0
P1:BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT ' ';
DECLARE stmt VARCHAR(228) ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
EH: BEGIN
get diagnostics EXCEPTION 1 o_MESSAGE = MESSAGE_TEXT;
VALUES (SQLSTATE,SQLCODE) INTO o_sqlstate, o_sqlcode;
END EH;
SET stmt='SET INTEGRITY FOR SCHEMA.'||TRIM(IN_TABLE_NAME)||' IMMEDIATE CHECKED';
EXECUTE IMMEDIATE stmt;
SET stmt='REFRESH TABLE SCHEMA.R_'||TRIM(IN_TABLE_NAME);
EXECUTE IMMEDIATE stmt;
END P1
@
-- Restore the default bindrules
call set_routine_opts(NULL)@
