-
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
-
Won't really work. . .
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
-
Just one question. . .
. . .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
-
Sorry about that. . .
. . .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)
-
I'll give it a shot
Thanks again. Our DBA will install the encryption package tomorrow and I will try it out.
I'll let you know what happened.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|