EXTREMELY URGENT - Suppressing Data

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.

For example:

CREATE VIEW Test_v AS
SELECT Decode( key_field, 'Manager', '---', key_field ), dept
FROM Test_Table;

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???

You can try something like

Select * from Test_v where key_field in
('---', 'Programmer', 'Consultant' );

This is not the best way but it will do the trick

your suggestion will return all rows where key_field is 'Manager'. I need to be able to return something like:

Key_Field Dept
__________________________
--- Finance
Consultant Trading
--- Sales


Thanks for the thought.

I have the perfect and coolest solution for you, give me about 30 min and a'll post it

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
BEGIN
RETURN TRIM(DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(input_string => RPAD(P_VAL, 400), key_string => 'ZZZZZZZZ'));
END;

CREATE OR REPLACE FUNCTION DECRYPT_TEST(P_VAL IN VARCHAR2,
P_KEY IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN TRIM(DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(input_string => RPAD(P_VAL, 400), key_string => P_KEY));
END;

CREATE OR REPLACE VIEW TEST_ENCRYPT AS
SELECT TABLE_NAME, ENCRYPT_TEST(COLUMN_NAME) COLUMN_NAME
FROM ALL_TAB_COLUMNS

SELECT *
FROM TEST_ENCRYPT;

SELECT TABLE_NAME, COLUMN_NAME, DECRYPT_TEST(COLUMN_NAME, 'ZZZZZZZZ')
FROM TEST_ENCRYPT

. . .where do I get the package DBMS_OBFUSCATION_TOOLKIT? It does not seem to be part of regular Oracle 8i installation.

Thanks for your attention in this matter.

I think it is but you should talk to your dba

. . .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,
Dept
FROM test_table;

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.

What do you think?

It will if your "where" will look like this:

where DECRYPT_TEST(empid, 'zzzzzzzz') in (1234, 5643, 3445)

Thanks again. Our DBA will install the encryption package tomorrow and I will try it out.

I'll let you know what happened.

I think I am getting closer, but the problem is still with the WHERE clause on the view. It is not possible for the users to create a where clause like:
where DECRYPT_TEST(empid, 'zzzzzzzz') in (1234, 5643, 3445)

They are using an OLAP software package from COGNOS and are not proficient enough in Oracle to be able to use package functions. Their SQL is generated by the software package via their column selections and criteria settings from the OLAP tool.

Any thoughts?