This content has been marked as final. Show 10 replies
earlier I have used the below script for the same requirement...if it is useful for you ... U can ...!!
CREATE OR REPLACE PACKAGE DUMMY_CRYPTO AS FUNCTION ENCRYPT (VAL IN VARCHAR2) RETURN VARCHAR2; FUNCTION DECRYPT (VAL IN VARCHAR2) RETURN VARCHAR2; END; CREATE OR REPLACE PACKAGE BODY DUMMY_CRYPTO AS FUNCTION ENCRYPT (VAL IN VARCHAR2) RETURN VARCHAR2 IS L_KEY RAW(128) := UTL_RAW.CAST_TO_RAW('ORACLE NETWORK TECH'); L_VAL RAW(128) := UTL_RAW.CAST_TO_RAW(VAL); L_ENCRYPTED_RAW RAW(2048); BEGIN L_ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => L_VAL, TYP => DBMS_CRYPTO.DES_CBC_PKCS5, KEY => L_KEY); RETURN UTL_RAW.CAST_TO_VARCHAR2(L_ENCRYPTED_RAW); END; FUNCTION DECRYPT (VAL IN VARCHAR2) RETURN VARCHAR2 IS L_KEY RAW(128) := UTL_RAW.CAST_TO_RAW('ORACLE NETWORK TECH'); L_VAL RAW(128) := UTL_RAW.CAST_TO_RAW(VAL); L_DECRYPTED_RAW RAW(2048); BEGIN L_DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC => L_VAL, TYP => DBMS_CRYPTO.DES_CBC_PKCS5, KEY => L_KEY); RETURN UTL_RAW.CAST_TO_VARCHAR2(L_DECRYPTED_RAW); END; END DUMMY_CRYPTO; EXECUTE THIS QUERY ****************** SELECT DUMMY_CRYPTO.ENCRYPT(WEB_LOGIN), DUMMY_CRYPTO.DECRYPT(DUMMY_CRYPTO.ENCRYPT(WEB_LOGIN)) FROM TABLE_NAME; SELECT DUMMY_CRYPTO.ENCRYPT(WEB_PASSWORD), DUMMY_CRYPTO.DECRYPT(DUMMY_CRYPTO.ENCRYPT(WEB_PASSWORD)) FROM TABLE_NAME;
Thanks for your response. This is not working in my scenario.Actually I want to encyrpt/hide columns in database for all users except those who are connecting through FORMS.
Our end user access the data using Forms so they can view the all columns of table, but when any other user including DBA/Vendors try to access the table from any tool then he can access only those columns which are not encrypted or hide. Encrypted columns are only display for FORMS user not for others. After that exercise we can decrypt the encrypted columns.
again thanks for your reply and time.
For this, you may not need to encrypt/decrypt data.
You may try to
1) create views (based on selected columns for which you want to give the access to vendor) on your tables.
2) Create another user in database (by which Vendor will log on)
3) Provide necessary privillages on the view to newly created user.
Thanks, First thing is that we have to keep the table name same for vendor as well, so if we create view then it will be different name.
I already created a view with different name and hide those columns and then created a public synonym for that view ( same name as table) and also created a user for vendor
give necessary permission to him. But I my question is that the table column be encrypt and vendor can see the name of column but not data. But the Form user (end user) can
view all data as their normal practice.