Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
ORA-01400 Error in application
Answers
-
Hi,
Thank you for taking the time to help me out.
This is the trigger I created. It works perfectly for incrementing and inserting my primary key. What I also need the trigger to do is take the password that is typed in and encrypt it.create or replace TRIGGER encrypt_pw_t1 BEFORE INSERT on encrypt_pw FOR EACH ROW BEGIN IF :new.pw_id IS NULL THEN SELECT pw_id2_seq.nextval INTO :new.pw_id FROM DUAL; END IF; END;
These are the two tables that are being used with the package and functions:CREATE TABLE ENCRYPT_PW ( PW_ID INTEGER CONSTRAINT PW_ID2_PK PRIMARY KEY, CLIENTID VARCHAR2(50 BYTE), CLIENT_CODE VARCHAR2(50 BYTE), PASSWORD VARCHAR2(256 BYTE), CLIENT_NAME VARCHAR2(256 BYTE) ); CREATE TABLE ED_ENCRYPT ( PK VARCHAR2(128 BYTE), EK RAW(128) );
This is the package and functions being created:CREATE OR REPLACE PACKAGE ENC_CLIENT AS FUNCTION ENC_PASSWORD( p_password in VARCHAR2, p_clientid in VARCHAR2, p_unlock_code in VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; FUNCTION DEC_PASSWORD( p_password in VARCHAR2, p_clientid in VARCHAR2, p_unlock_code in VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY ENC_CLIENT AS g_master_key VARCHAR2(32) := 'ThisIsTheSuperSe'; g_unlock_code VARCHAR2(10) := 'OpenSesame'; l_mod number := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5; FUNCTION enc_password ( p_password in VARCHAR2, p_clientid in VARCHAR2, p_unlock_code in VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS l_key RAW(256); l_stored_key RAW(256); BEGIN IF (p_unlock_code IS NULL OR p_unlock_code != g_unlock_code) THEN RETURN NULL; END IF; -- Random key generation l_key := dbms_crypto.randombytes(16); -- Encrypts random key with master key into a stored key l_stored_key := dbms_crypto.encrypt(l_key, l_mod, UTL_I18N.STRING_TO_RAW(g_master_key, 'AL32UTF8') ); -- store unique value and stored key in table BEGIN DELETE ED_ENCRYPT WHERE PK = p_clientid; END; INSERT INTO ED_ENCRYPT VALUES (p_clientid, l_stored_key); -- returns encrypted salary that is encrypted with the random key RETURN UTL_ENCODE.BASE64_ENCODE(dbms_crypto.encrypt(UTL_I18N.STRING_TO_RAW(p_password,'AL32UTF8'), l_mod, l_key)); END; FUNCTION dec_password ( p_password in VARCHAR2, p_clientid in VARCHAR2, p_unlock_code in VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS l_key RAW(256); BEGIN IF (p_unlock_code IS NULL OR p_unlock_code != g_unlock_code) THEN RETURN NULL; END IF; SELECT dbms_crypto.decrypt(ek, l_mod, UTL_I18N.STRING_TO_RAW(g_master_key,'AL32UTF8')) INTO l_key FROM ED_ENCRYPT WHERE pk = p_clientid; RETURN UTL_I18N.RAW_TO_CHAR(dbms_crypto.decrypt(UTL_ENCODE.BASE64_DECODE(p_password),l_mod,l_key),'AL32UTF8'); END; END; /
If there is anything else that is needed, please let me know.
Thanks again for your help. -
Hi,
I hope that I'm reading your code correctly.... I think your trigger should be:CREATE OR REPLACE TRIGGER ENCRYPT_PW_T1 BEFORE INSERT OR UPDATE ON ENCRYPT_PW FOR EACH ROW BEGIN IF :NEW.PW_ID IS NULL THEN SELECT PW_ID2_SEQ.NEXTVAL INTO :NEW.PW_ID FROM DUAL; END IF; IF INSERTING THEN :NEW.PASSWORD := ENC_CLIENT.ENC_PASSWORD(:NEW.PASSWORD, :NEW.CLIENTID, 'OpenSesame'); ELSE IF :NEW.PASSWORD <> :OLD.PASSWORD THEN :NEW.PASSWORD := ENC_CLIENT.ENC_PASSWORD(:NEW.PASSWORD, :NEW.CLIENTID, 'OpenSesame'); END IF; END IF; END;
This will handle inserts and updates. The code should first check if a new PW_ID PK value is needed, and set it if it is. Then, for inserts only, it will encrypt the password entered - I'm assuming here that the password will be "plain text" and requires encryption. For updates only, check if the current password matches the old password (comparing a new decrypted version to the old decrypted version) - if it doesn't match (ie, the password has been changed), then update the password value with the encrypted new password.
Unfortunately, the dbms_crypto package does not seem to be available on my OTN workspace (not sure why), so I have not been able to test this.
Andy -
Hi Andy,
Thank you for the help. I actually was not that far off. Instead of referencing :NEW_PASSWORD, I was referencing password.
I will test this out and see how it goes.
Thanks a bunch. You were very helpful. -
Hi Andy,
I just wanted to give you an update. Your fix worked. As I suspected, the error of my way was using the actual field names from the table and not the :NEW references.
Thank you and Thank you again. You were a great help! -
You're welcome!
This discussion has been closed.