Oracle Data Masking enables the possibility to create entire obfuscated copies to be extracted from the database. This is perfect if you, for example, require full production copies in your test environment.
This post is not about how you do a data masking in detail. It is about problems we had during the configuration and implementation.
When you are implementing data masking, you are using the Oracle Enterprise Manager. That is the recommended way.
Unfortunately, I must say that the data masking part of the OEM has a horrendously bad interface. The design doesn’t follow a logic perspective. It is so much more to wish for. That itself would be a very long blog post. But after spending a few DAYS you will get a hang of it.
Brief Overview of our data masking process
To start the data masking process, you need to create an Application Data Model based on your source database. The ADM stores the list of applications, tables, and relationships between table columns that are either declared in the data dictionary, imported from application metadata, or user-specified. You can let Oracle discover sensitive data or user-specified them. After that is done it is time to create a data masking definition.
In short. Within the data masking definition, you define how your sensitive columns should be obfuscated.
When all this is done you have to decide if you want to mask in-database or in-export file. After this we let Oracle generate the masking script.
We did a clone of our production database and used that as the masking target, later moving that obfuscated clone to our test environment.
The first problem we encountered was with executing the masking job. Time after time it failed because of lacking privileges. But those problems were easily solved since the log showed us the information we needed.
When running the job we got ” Data masking job fails with
ERROR executing steps ORA-01927: cannot REVOKE privileges you did not grant errorExitOraError! Completed Data Masking. Starting cleanup phase.
After some investigations we found out that there is an enhancement request for this (Doc ID 2395777.1). Without any further explanation, Oracle’s suggestion is to ” Regenerate the masking script and execute the masking job. “. That did work, but I can’t explain why, and you have to remember that every time. This is a bug!
Next problem. Some column name in the application have å,ä,ö characters (yes that is bad). Those types of characters will make the masking job fail.
Error found : ORA-00904: "X"."X??XX_XX": invalid identifier Masking job Failed
The solution to that was to rename the columns, mask and rename back.
Hidden columns will make the masking job fail. In our case, it was multipel function-based index that was the root cause. Oracle creates column starting with SYS_NC that are hidden.
How do you now that you have a hidden column?
select column_name from user_tab_cols where column_id is null
Extended statistics may also be a problem. Since Oracle creates a hidden columns.
create table tab1(col1 number,col2 number); Table TAB1 created. select column_name from user_tab_cols where table_name= 'TAB1' and column_id is null; COLUMN_NAME ------------------------------ SYS_STUFLHATC5RBD6JHJZWT$X2AAH select table_name,extension_name,extension from user_stat_extensions; TABLE_NAME EXTENSION_NAME EXTENSION ---------- ----------------------------------------------------- TAB1 SYS_STUFLHATC5RBD6JHJZWT$X2AAH ("COL1","COL2")
This should of course just work out of the box, from my point of view. Dropping function-based index, extended statistics, etc, should not be necessary when doing data masking.
Oracle Data masking is a very useful and cool product but it has great potential to become so much better.
Over & Out