DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: EXTREMELY URGENT - Suppressing Data

  1. #1
    Join Date
    Mar 2002
    Posts
    14

    Exclamation

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

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    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

  3. #3
    Join Date
    Mar 2002
    Posts
    14

    Angry 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.

  4. #4
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    I have the perfect and coolest solution for you, give me about 30 min and a'll post it

  5. #5
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    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

  6. #6
    Join Date
    Mar 2002
    Posts
    14

    Question 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.

  7. #7
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    I think it is but you should talk to your dba

  8. #8
    Join Date
    Mar 2002
    Posts
    14

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

  9. #9
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    It will if your "where" will look like this:

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

  10. #10
    Join Date
    Mar 2002
    Posts
    14

    Smile 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
  •  


Click Here to Expand Forum to Full Width