Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at https://ideas.ibm.com


Status Future consideration
Workspace Db2 for z/OS
Created by Guest
Created on Sep 5, 2023

Masking function with GRANT per column

Column masking has been great help in managing sensitive data and complying with the General Data Protection Regulation (GDPR).
But today’s solution to know if a user has access to data without masking is to create a Mask using a CASE with SESSION_USER or VERIFY_GROUP_FOR_USER.
But when you have a Mask defined, you have many restrictions to select the data, receiving SQLCODE -20478, even if the user has authority to access the data.

This is a sample of the test we did to get the error:


- Table with column mask:
CREATE TABLE DB2TST.USER (
ID_USER INT NOT NULL
GENERATED BY DEFAULT AS IDENTITY,
ST_NAME VARCHAR(20) NOT NULL,
ST_EMAIL VARCHAR(20) NOT NULL,
DT_BIRTHDAY DATE NOT NULL
);
CREATE MASK DB2TST.EMAIL_MASK ON DB2TST.USER
FOR COLUMN ST_EMAIL RETURN
CASE
WHEN (
VERIFY_GROUP_FOR_USER(SESSION_USER,'GRPDBA') = 1)
THEN ST_EMAIL
ELSE 'xxxx@xxxxxxx.xxx'
END
ENABLE;


- Query (SELECT *) from USER1 member of group GRPDBA:
SELECT * FROM DB2TST.USER;
---------+---------+---------+---------+---------+---------+---------
ID_USER ST_NAME ST_EMAIL DT_BIRTHDAY
---------+---------+---------+---------+---------+---------+---------
1 maria maria@email.com 14.05.1982
2 joao joao@email.com 14.05.1982
3 ana ana@email.com 14.05.1982
4 jose jose@email.com 14.05.1982


- Query (SELECT CASE) from USER1 member of group GRPDBA:
SELECT
CASE
WHEN ST_EMAIL LIKE 'MARIA%' THEN 'OK'
ELSE 'NOT OK'
END
FROM DB2TST.USER;
---------+---------+---------+---------+---------+---------+---------
DSNT408I
SQLCODE = -20478, ERROR: THE STATEMENT CANNOT BE PROCESSED BECAUSE
COLUMN MASK DB2TST.EMAIL_MASK (DEFINED FOR COLUMN USER.ST_EMAIL)
EXISTS AND THE COLUMN MASK CANNOT BE APPLIED OR THE
DEFINITION OF THE MASK CONFLICTS WITH THE REQUESTED STATEMENT. REASON CODE 24


This is a big problem to us, as we have a Data Warehousing Datasharing Group, where many analysts can access the data, using many functions that are restricts because the Masking.
We need an implementation to solve this problem. We would like that if the user has authority to access the data, he can use any option available to do a Select. With GRANT he can SELECT anything, and the MASK function is not started, so no restrictions exists.
Needed By Yesterday (Let's go already!)
  • Guest
    Reply
    |
    Sep 16, 2023

    I understand your point now with the above example! We might plan to lift some column mask restrictions(SQLCODE -20478) including reason code 24, will it be okay for above scenario?

  • Guest
    Reply
    |
    Sep 5, 2023

    Db2 provided a built-in scalar function VERIFY_GROUP_FOR_USER, it could be used in column mask definition to decide whether a user can get mask or unmask value of the column.

    In the following example, the EMPLOYEE table has column access control enabled. If the connection

    is established outside a trusted context and Mary, who has a secondary authorization ID of "MGR",

    queries the social security number of Tom from the EMPLOYEE table, the social security number is

    returned. When Mary is no longer a manager, the same query displays the last four digits of Tom's

    social security number.

    CREATE MASK SSN_MASK ON EMPLOYEE

    FOR COLUMN SSN

    RETURN

    CASE WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'MGR') = 1

    THEN SSN

    ELSE 'XXX-XX-' || SUBSTR(SSN, 8, 4)

    END

    ENABLE;

    COMMIT;

    An ALTER TABLE statement is then issued to activate the column mask on the EMPLOYEE table:

    ALTER TABLE EMPLOYEE

    ACTIVATE COLUMN ACCESS CONTROL;

    COMMIT;

    Mary connects to Db2, issues the following query, then disconnects from Db2:

    SELECT SSN

    FROM EMPLOYEE

    WHERE NAME = 'Tom';

    Mary receives Tom's social security number.

    When Mary is no longer a manager, the secondary authorization ID, MGR is removed for her

    authorization ID. The next time Mary connects to Db2 and issues the following command, only the

    last four digits of Tom's social security number are displayed because of the column mask SSN_MASK:

    SELECT SSN

    FROM EMPLOYEE

    WHERE NAME = 'Tom'