Does anyone have a suggestion on how to mask out data for a key field in a table?
I have exhausted a number of different options. Creating a view works just fine, except for when you need to use a WHERE clause. If the key field in the select is masked, then the WHERE clause on the view can't find a match.
CREATE VIEW Test_v AS
SELECT Decode( key_field, 'Manager', '---', key_field ), dept
This works just fine:
Select * from Test_v
But this does not:
Select * from Test_v where key_field in
('Manager', 'Programmer', 'Consultant' );
Rows for Programmer and Consultant are returned, but the row for Manager is not with the key_field masked and the rest of the fields displayed.
I understand why this is happenning, the Where clause can't match up 'Manager' to '---'. The question is what to do to get this to work???
This will work like a charm, please let me know if it did.
CREATE OR REPLACE FUNCTION ENCRYPT_TEST(P_VAL IN VARCHAR2)
RETURN VARCHAR2 AS
RETURN TRIM(DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input_string => RPAD(P_VAL, 400), key_string => 'ZZZZZZZZ'));
CREATE OR REPLACE FUNCTION DECRYPT_TEST(P_VAL IN VARCHAR2,
P_KEY IN VARCHAR2)
RETURN VARCHAR2 AS
RETURN TRIM(DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(input_string => RPAD(P_VAL, 400), key_string => P_KEY));
CREATE OR REPLACE VIEW TEST_ENCRYPT AS
SELECT TABLE_NAME, ENCRYPT_TEST(COLUMN_NAME) COLUMN_NAME
SELECT TABLE_NAME, COLUMN_NAME, DECRYPT_TEST(COLUMN_NAME, 'ZZZZZZZZ')
. . .you are absolutely correct, it is part of Oracle. We don't have it since we are still on Oracle 8i release 8.1.6.
In any case, the concept is great. One problem remains. Let me give you a better example which represents more clearly what I am trying to do.
Data Table - test_table
EmployeeID Last_Name First_Name Dept
12245 Jones Barry 001
14557 Green Frank 002
98774 Brown Tony 001
24557 Gilmore Dave 003
All employees in Dept = '001' must have encrypted employee ID.
CREATE VIEW test_v AS
SELECT Encrypted( EmployeeID ), Last_name, First_Name,
If I issue a SELECT * FROM test_v, it works just fine.
The problem is if I say:
SELECT * FROM test_v
WHERE EmployeeID IN ('12245', '14557', '98774', '24557');
This will probably not work, since the view encrypts the EmployeeID field and the WHERE clause will not be able to match up the items in the IN list.