Personal Information in Informix

Hi Guys, What do you think would be the best way to implement access to Personal Identifiable Information (PII) within Informix? What a customer wants to achieve is, when accessing (selecting from) a table containing PII, to show (1) the real value of a column, say the person´s Name or Social Security number if a user (or role) with 'high' privileges is looking (selecting) at it; or (2) the data being masked if another user (or role) with lesser privileges goes and selects from the table. I guess there must be a number of different ways of implementing this. Which would you think is the best ? Thanks for your ideas ! Josem #Informix

Hi Jose, We have done this using Informix column level encryption. It is a bit more complex to explain in an email but I did a Webcast on this a couple of years ago. The Webcast replay is at How to Secure your Data - Informix Column Encryption and Security - Advanced DataTools Enjoy - Lester ------------------------------ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ------------------------------

Hello Jose, To implement the masking feature in the database, there is a way to utilize the encryption feature Lester mentioned. There is also a third-party solution that provides a separate encryption library. In Korea, ARIA or SEED algorithms are also used. When data is encrypted, there is a possibility that the existing schema or index needs to be changed. If there is a lot of data to be encrypted, as the column size increases, more space is required to store the data, which may degrade system performance. In some cases, separate solutions such as IBM Guardium Data Protection are used to minimize application changes or database degradation and to manage keys. Of course, there are other solutions than IBM. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Looks like a perfect fit for the Informix Label Based Access Control (LBAC) - https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sec.doc/ids_lb_002.htm ------------------------------ Vladimir Kolobrodov ------------------------------

Vladimir: I thought about using LBAC as well, but the column with the security label defined cannot be queried. The 8245 error occurs as in the example below. Is there a way to apply the column security label and run the query without error? --- Define the Security Policies and Label > CREATE SECURITY LABEL COMPONENT slc_level ARRAY ['HIGHLY CONFIDENTIAL', 'CONFIDENTIAL', 'UNCLASSIFIED']; Security label component created. > CREATE SECURITY POLICY access_employee_policy COMPONENTS slc_level WITH IDSLBACRULES RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL; Security policy created. > CREATE SECURITY LABEL access_employee_policy.HIGHCONFIDENTIAL COMPONENT slc_level 'HIGHLY CONFIDENTIAL'; Security label created. > CREATE SECURITY LABEL access_employee_policy.CONFIDENTIAL COMPONENT slc_level 'CONFIDENTIAL'; Security label created. > CREATE SECURITY LABEL access_employee_policy.UNCLASSIFIED COMPONENT slc_level 'UNCLASSIFIED'; Security label created. --- Create the EMP table and load the sample data > CREATE TABLE EMP ( EMPNO CHARACTER(6), FIRSTNAME VARCHAR(12), MIDINIT CHARACTER(1), LASTNAME VARCHAR(15), WORKDEPT CHARACTER(3), PHONENO CHARACTER(4), GENDER CHARACTER(1), HIREDATE DATE, JOB CHARACTER(8), EDLEVEL SMALLINT, BIRTHDATE DATE, SALARY DECIMAL(9), BONUS DECIMAL(9),COMMISSION DECIMAL(9)); Table created. > INSERT INTO EMP VALUES ('1001', 'Priyanka', '', 'Chopra', 'mkt', '1234', 'f', '1/3/08', 'manager', 10, '10/8/84', 89000, 10000, 20000);1 row(s) inserted. --- Protect the EMP table > ALTER TABLE EMP MODIFY(EMPNO CHARACTER(6) COLUMN SECURED WITH CONFIDENTIAL), MODIFY(FIRSTNAME VARCHAR(12) COLUMN SECURED WITH UNCLASSIFIED), MODIFY(MIDINIT CHARACTER(1) COLUMN SECURED WITH UNCLASSIFIED), MODIFY(LASTNAME VARCHAR(15) COLUMN SECURED WITH UNCLASSIFIED), MODIFY(WORKDEPT CHARACTER(3) COLUMN SECURED WITH UNCLASSIFIED), MODIFY(PHONENO CHARACTER(4) COLUMN SECURED WITH UNCLASSIFIED), MODIFY(GENDER CHAR(1) COLUMN SECURED WITH UNCLASSIFIED), MODIFY(HIREDATE DATE COLUMN SECURED WITH CONFIDENTIAL), MODIFY(JOB CHARACTER(8) COLUMN SECURED WITH CONFIDENTIAL), MODIFY(EDLEVEL SMALLINT COLUMN SECURED WITH CONFIDENTIAL), MODIFY(BIRTHDATE DATE COLUMN SECURED WITH HIGHCONFIDENTIAL), MODIFY(SALARY DECIMAL(9) COLUMN SECURED WITH HIGHCONFIDENTIAL), MODIFY(BONUS DECIMAL(9) COLUMN SECURED WITH HIGHCONFIDENTIAL), MODIFY(COMMISSION DECIMAL(9) COLUMN SECURED WITH HIGHCONFIDENTIAL),ADD SECURITY POLICY access_employee_policy; Table altered. --- Grant the Security Labels to users > GRANT SECURITY LABEL access_employee_policy.CONFIDENTIAL TO USER skjeong FOR READ ACCESS;Security label granted. --- Watch the LBAC solution in action [skjeong@db2 ids1410fc3]$ dbaccess testdb - Database selected. > select empno, salary, bonus from emp;8245: User cannot perform READ access to the protected column (salary). Error in line 1Near character position 35 > select empno, firstname, job from emp;empno firstname job 1001 Priyanka manager 1 row(s) retrieved. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Looking at the requirement: > "the data being masked if another user (or role) with lesser privileges goes and selects from the table" it seems that it's exactly what LBAC would do for the privileged data. My understanding is that the error would be expected if protected / privileged column is explicitly specified, similar to what would happen if one tries to select column that does not exist. However queries using for example simple "select * from table" containing columns requiring elevated privilege will not display columns with privileged data. Someone who has more experience with LBAC can comment on this further, but clearly implementing LBAC will require some thought for designing application side correctly as well - since tables will appear to have different number of columns for users with different privilege. ------------------------------ Vladimir Kolobrodov ------------------------------

Yes, LBAC will do the job. Simple column privs will work also except for the requirement to return garbage.

Thank you for the explanation. My mistake. I understood that the masked value should be returned to the user for that phrase. > "the data being masked if another user (or role) with lesser privileges goes and selects from the table" ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------