Issue while using a non-deterministic function in create mask return case statement
Posted: Fri Dec 31, 2021 11:16 am
I am trying to create an IBMi DB2 mask over a table as below:
CREATE or replace MASK IESWEBSERP.MASK_PWD_WSCONTROL ON IESWEBSERP.WSCONTROL
FOR COLUMN WSVNDPWD RETURN
CASE
WHEN ( VERIFY_GROUP_FOR_USER(SESSION_USER, 'ICC', 'IICCGRP')=1)
THEN WSVNDPWD
WHEN ( SESSION_USER in ('QSQSRVR', 'QTMHHTTP', 'PROFOUNDJS', 'IESWEBSVR'))
THEN WSVNDPWD
WHEN IESWEBSERP.IES_CHECK_AUTH( WSDTALIB ) = 1
THEN WSVNDPWD
WHEN (WSVNDID = IESWEBSERP.web_login_id )
THEN WSVNDPWD
ELSE 'MASKED'
END
ENABLE ;
The problem is with the IESWEBSERP.IES_CHECK_AUTH user defined function used in the RETURN CASE.
As per the documentation, I understand that the RETURN CASE statement cannot use a Non-Deterministic function. So below is how this udf is defined:
Create or REPLACE Function IESWEBSERP.IES_CHECK_AUTH(
DATA_LIBRARY CHAR(10))
returns dec(1,0)
language SQL
NO EXTERNAL ACTION
DETERMINISTIC
NOT FENCED
SECURED
BEGIN
declare q char(1) ;
declare back char(1) ;
declare @sqlStmt1 varchar(500) ;
declare @sqlStmt varchar(500) ;
declare myCursor cursor for myStatement;
declare myCursor1 cursor for myStatement1;
set q = '''';
set @sqlStmt1 = 'select count(*) from QSYS2.TABLES WHERE TABLE_SCHEMA = '
concat q concat TRIM(DATA_LIBRARY) concat q
concat ' and TABLE_NAME = ' concat q concat 'MSTCONTL' concat q;
prepare myStatement1 from @SqlStmt1;
open myCursor1;
Fetch myCursor1 into back;
close myCursor1;
IF ( back = '1' )
THEN
SET back = '0' ;
set @sqlStmt = 'select count(*) from ' concat TRIM(DATA_LIBRARY) concat '.MSTCONTL
where USRID = SESSION_USER and (USRC2 =' concat q concat 'U' concat q
concat 'or USRC2 = ' concat q concat 'P' concat q concat')';
prepare myStatement from @SqlStmt;
open myCursor;
Fetch myCursor into back;
close myCursor;
IF ( back = '1' )
THEN SET back = '1' ;
ELSE SET back = '0' ;
END IF ;
ELSE SET back = '0' ;
END IF ;
return(back );
END;
All the UDF does is it checks if value of field MSTCONTL.USRC2 is 'U' or 'P' for the user record of the current session user. If it is so, it returns a value of '1' which would allow the user to see the value of the field WSVNDPWD without any masking.
But the catch is that if the value MSTCONTL.USRC2 is updated to a value other than U or P, the function would still return '1' (if '1' was returned earlier as well).. This I believe is because the function is defined as DETERMINISTIC.
If I make the function non DETERMINSTIC, the create mask statement fails. So I am not sure how to handle this situation. I want a "dynamic" result from the function. Please can someone advise how this can be handled?
CREATE or replace MASK IESWEBSERP.MASK_PWD_WSCONTROL ON IESWEBSERP.WSCONTROL
FOR COLUMN WSVNDPWD RETURN
CASE
WHEN ( VERIFY_GROUP_FOR_USER(SESSION_USER, 'ICC', 'IICCGRP')=1)
THEN WSVNDPWD
WHEN ( SESSION_USER in ('QSQSRVR', 'QTMHHTTP', 'PROFOUNDJS', 'IESWEBSVR'))
THEN WSVNDPWD
WHEN IESWEBSERP.IES_CHECK_AUTH( WSDTALIB ) = 1
THEN WSVNDPWD
WHEN (WSVNDID = IESWEBSERP.web_login_id )
THEN WSVNDPWD
ELSE 'MASKED'
END
ENABLE ;
The problem is with the IESWEBSERP.IES_CHECK_AUTH user defined function used in the RETURN CASE.
As per the documentation, I understand that the RETURN CASE statement cannot use a Non-Deterministic function. So below is how this udf is defined:
Create or REPLACE Function IESWEBSERP.IES_CHECK_AUTH(
DATA_LIBRARY CHAR(10))
returns dec(1,0)
language SQL
NO EXTERNAL ACTION
DETERMINISTIC
NOT FENCED
SECURED
BEGIN
declare q char(1) ;
declare back char(1) ;
declare @sqlStmt1 varchar(500) ;
declare @sqlStmt varchar(500) ;
declare myCursor cursor for myStatement;
declare myCursor1 cursor for myStatement1;
set q = '''';
set @sqlStmt1 = 'select count(*) from QSYS2.TABLES WHERE TABLE_SCHEMA = '
concat q concat TRIM(DATA_LIBRARY) concat q
concat ' and TABLE_NAME = ' concat q concat 'MSTCONTL' concat q;
prepare myStatement1 from @SqlStmt1;
open myCursor1;
Fetch myCursor1 into back;
close myCursor1;
IF ( back = '1' )
THEN
SET back = '0' ;
set @sqlStmt = 'select count(*) from ' concat TRIM(DATA_LIBRARY) concat '.MSTCONTL
where USRID = SESSION_USER and (USRC2 =' concat q concat 'U' concat q
concat 'or USRC2 = ' concat q concat 'P' concat q concat')';
prepare myStatement from @SqlStmt;
open myCursor;
Fetch myCursor into back;
close myCursor;
IF ( back = '1' )
THEN SET back = '1' ;
ELSE SET back = '0' ;
END IF ;
ELSE SET back = '0' ;
END IF ;
return(back );
END;
All the UDF does is it checks if value of field MSTCONTL.USRC2 is 'U' or 'P' for the user record of the current session user. If it is so, it returns a value of '1' which would allow the user to see the value of the field WSVNDPWD without any masking.
But the catch is that if the value MSTCONTL.USRC2 is updated to a value other than U or P, the function would still return '1' (if '1' was returned earlier as well).. This I believe is because the function is defined as DETERMINISTIC.
If I make the function non DETERMINSTIC, the create mask statement fails. So I am not sure how to handle this situation. I want a "dynamic" result from the function. Please can someone advise how this can be handled?