Single permission with a UDF

Example 4: Using a single permission with a User Defined Function (UDF).

           CREATE SCHEMA RCAC_DEPENDENT  
           CREATE SCHEMA MY_LIB  
           CREATE TABLE MY_LIB.PERMISSION_TABLE (COLUMN1  INT)
           CREATE OR REPLACE FUNCTION RCAC_DEPENDENT.UDF_PERMISSION
                   ()
                     RETURNS CHAR(10)
                     LANGUAGE SQL
                     MODIFIES SQL DATA
                     NO EXTERNAL ACTION
                     DETERMINISTIC
                     NOT FENCED
                     SECURED
                     BEGIN
                       DECLARE ALLOWS CHAR(10);
                        IF  (CURRENT_USER = 'USER1') THEN
                           SET ALLOWS = 'ALLOWED   ';
                        ELSE
                           SET ALLOWS = 'DISALLOWED';   END IF;
                        RETURN ALLOWS;
                     END

           CREATE PERMISSION MY_LIB.PERMISSION_USER 
                               ON MY_LIB.PERMISSION_TABLE
                     FOR ROWS WHERE
                      RCAC_DEPENDENT.UDF_PERMISSION()  = 'ALLOWED   '
                      ENFORCED FOR ALL ACCESS  ENABLE
 
            ALTER TABLE MY_LIB.PERMISSION_TABLE ACTIVATE ROW ACCESS CONTROL

  

The advantage of a single permission checking a UDF is adding another user, the permission does not have to change. The disadvantage appears when the UDF changed. During the next open of the table with the permission, verification must be done to allow the new UDF to be used with the permission. The verification causes the permission or mask to be regenerated once for the table.