Hi Mayank,
We have opened a conversation with the engineering team, as this is clearly functionality that would improve the profiling of Siebel. We also encountered this issue and whilst there is a work-around this creates additional manual effort, which is undesirable. Hopefully we can get this feature added to a future release.
It stands to reason that there are always a number of manual checks needed to find sensitive data as well as to check for profiler false positives and false negatives. The objective is to reduce these to a minimum by creating a bespoke profiler, as I imagine you are doing. There are a number of improvements that the engineering team are working on as part of the ongoing product development.
One thing that we did with the Siebel subject matter experts was to try to identify potentially sensitive data to remove tables that were not used or not needed, pre-processing the data for them to identify potentially sensitive data tables and columns.
For instance the following check of the User Columns which had a high number of distinct values was useful in narrowing down the search for sensitive data:
SELECT TABLE_NAME, COLUMN_NAME FROM USER_TAB_COLUMNS WHERE NUM_DISTINCT > 1000
This could be further refined by searching for specific column names:
SELECT TABLE_NAME, COLUMN_NAME FROM USER_TAB_COLUMNS WHERE NUM_DISTINCT > 1000 AND COLUMN_NAME LIKE '%NUM' OR COLUMN_NAME LIKE '%NAME';
A further refinement might be to use the regular expressions:
SELECT TABLE_NAME, COLUMN_NAME FROM USER_TAB_COLUMNS WHERE NUM_DISTINCT > 1000 AND (REGEXP_LIKE (COLUMN_NAME, '^NUM');
Obviously it would be ideal if the profiler did these things automatically. It's useful however that we operate on virtual databases as this allows us to make modifications as needed to improve the masking and profiling performance and test and rewind and keep branched versions for test purposes.
There is some internal chatter about this feature, so hopefully we can announce some improvements for you in the near future.
Thanks for highlighting this issue and bringing it to our attention.
Regards,
Gary