IBM Db2 LUW connections

DataMasque masks IBM Db2 LUW tables in place.

• Required privileges
• Limitations
• Supported data types
• Unsupported key columns

Required privileges

A DataMasque connection for Db2 LUW requires at least the following permissions:

• SELECT permission on the SYSIBMADM.ADMINTABINFO, SYSIBMADM.ENV_INST_INFO and SYSIBM.SYSDUMMY1 tables.
• SELECT permission on the following SYSCAT tables:
  • TABLESPACES
  • TABLES
  • COLUMNS
  • REFERENCES
  • KEYCOLUSE
  • INDEXES
  • INDEXCOLUSE
  • TABCONST
• Some tasks require CREATEIN, DROPIN, ALTERIN, INSERTIN, and UPDATEIN permissions on the default schema for the connection, regardless of whether the table(s) being masked are in the default schema.
• Because DataMasque performs REORG TABLE commands in some masking tasks, masking requires the CONTROL permission on any table to be masked.

In the below commands, you can replace ROLE <role> with USER <user> or GROUP <group> as appropriate.

GRANT SELECT ON SYSIBMADM.ADMINTABINFO TO ROLE <role>;
GRANT SELECT ON SYSIBMADM.ENV_INST_INFO TO ROLE <role>;
GRANT SELECT ON SYSIBM.SYSDUMMY1 TO ROLE <role>;

GRANT SELECT ON SYSCAT.TABLESPACES TO ROLE <role>;
GRANT SELECT ON SYSCAT.TABLES TO ROLE <role>;
GRANT SELECT ON SYSCAT.COLUMNS TO ROLE <role>;
GRANT SELECT ON SYSCAT.REFERENCES TO ROLE <role>;
GRANT SELECT ON SYSCAT.KEYCOLUSE TO ROLE <role>;
GRANT SELECT ON SYSCAT.INDEXES TO ROLE <role>;
GRANT SELECT ON SYSCAT.INDEXCOLUSE TO ROLE <role>;
GRANT SELECT ON SYSCAT.TABCONST TO ROLE <role>;

GRANT CONTROL ON <schema>.<table> TO ROLE <role>;
GRANT CREATEIN, DROPIN, ALTERIN, INSERTIN, UPDATEIN ON SCHEMA <connection default schema> TO ROLE <role>;

Notes:

1. For build_temp_table tasks, you will require the same schema privileges as for masking on the schema in which the temporary table is to be created: GRANT CREATEIN, DROPIN, ALTERIN, INSERTIN, UPDATEIN ON SCHEMA <schema> TO ROLE <role>

2. The permissions on the system tables (SYSIBM, SYSIBMADM and SYSCAT schemas) are available to all users by default, unless they have been explicitly revoked or the database was created as RESTRICTIVE.

3. If a schema is not explicitly specified on the connection settings, the connection's default schema has the same name as the Db2 username specified in the connection settings. In this case, the permissions on the default schema are available to that Db2 user by default, unless they have been explicitly revoked.

Limitations

For from_unique_imitate masking runs which mask columns that rely on a value generated by a sequence, the sequence will be reset only if the sequence is referenced directly in a trigger definition (the TEXT column for any row in the SYSCAT.TRIGGERS), and not if the sequence is used implicitly in a trigger definition (e.g. via a stored procedure).

This is because in Db2 LUW, it is not possible to set a default value for a column as a NEXT VALUE of a sequence, thus, DataMasque will only reset a sequence if it is discovered in an existing INSERT trigger.

For example, the following trigger-defined sequence will be reset during a from_unique_imitate masking run:

CREATE TRIGGER set_seqid
BEFORE INSERT ON sequence_test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
    -- Explicit usage of sequence NEXT VALUE
    SET NEW.seqid = NEXT VALUE FOR detectable_sequence;
END;

However, the following sequence will not be reset:

CREATE PROCEDURE get_next_seqid (OUT next_seqid INT)
BEGIN
    SELECT NEXT VALUE FOR undetectable_sequence INTO next_seqid;
END;

CREATE TRIGGER set_seqid
BEFORE INSERT ON sequence_test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
    -- Implicit usage of sequence NEXT VALUE
    CALL get_next_seqid(NEW.seqid);
END;

Additionally, columns with values generated by an IDENTITY will fail to be masked.

• run_sql tasks can only execute one SQL statement per task.

• truncate_table tasks don't truncate in the SQL sense but deletes all rows instead (it uses DELETE FROM {table} rather than TRUNCATE TABLE {table}). This is important to consider for a few reasons:

Truncation	Deleting All Rows
Faster	Slower
One transaction log entry	One transaction log entry per row
Skips triggers	Activates triggers
Resets IDENTITY columns	Doesn't reset IDENTITY columns
Can't be used with foreign key constraints	Can be used with foreign key constraints

• timestamp values do not support timezones, requiring careful handling during masking:

  • Masking functions like from_random_datetime disregard any supplied timezone.
  • Masking functions like retain_age operate in UTC time for masking, treating values as if they are in UTC.
    • This means the age that retain_age will retain will be relative to UTC and not your local time.
  • Including timezones in date values used in if conditions will cause masking to fail.

• The BLOB data type and the from_blob mask are not supported.

• from_random_number: When generating a ruleset that masks numeric columns, the generator will pick a from_random_number mask with an incorrect upper bound for the column's type. Specifically it will choose 10^(number of bytes in column) - 1.
  For example when masking an integer column, a 4 byte column, it will choose an upper bound of 9999 instead of 2147483647 (2^31 - 1).

  • This can be manually adjusted in the ruleset editor to the correct value.

• No form of character set / codepage / encoding setting is available for Db2 LUW connections. DataMasque always interoperates with Db2 databases using the UTF-8 codepage, known as codepage 1208 in Db2. If the codepage of the target database is different from UTF-8, Db2 replaces any characters not in the database codepage with the substitute control character (codepoint 001a) when reading from, or writing to, the database.
  Ensure your ruleset's masked values, custom discovery match rules, and so on contain only characters from the target database's codepage.

Supported data types

The following IBM Db2 LUW data types are supported:

Data Type	Description
smallint	16-bit binary integer with range of -32768 to +32767 (inclusive)
integer	32-bit binary integer with range of -2147483648 to +2147483647 (inclusive)
bigint	64-bit binary integer with range of -2^63 (-9223372036854775808) to 2^63 - 1 (9223372036854775807) (inclusive)
real	Single precision floating-point number (32-bit).
double, float	Double precision floating-point number (64-bit).
decimal, numeric	Numerical value with 31 digit precision
char(n)	Fixed-length character string between n=1 and n=255 characters long (inclusive).
varchar(n)	Variable-length character string between n=1 and n=32704 characters long (inclusive).
date	Date value with no time.
time	Time value with no date.
timestamp	Date value with date and time, but no timezone.

Unsupported key columns

Data Type	Unsupported Format
real	All
double	All
float	All
decfloat	All
timestamp	Scale >= 7
decimal	Scale >= 31