I have a requirement, where I need to encrypt and decrypt data stored in database and during transit.
Data will be accessed by both stored procedure and java front end.
Is it possible to use java encryption to encrypt the data and stored in that format in DB then when stored procedure needs it it can decrypt the data?
In nutshell, is it possible to For java front end and stored procedure to use the same encryption and decryption methods?
if yes, can you please point me to more info.... so i can read about it
this is a totally new area for me and need your help
It has been more than 10 years since I last worked with Oracle RDBMS, so my knowledge about some of its capabilities will be a litle rusty. So take this as general guidance that requires more research on your part before you come up with the final design.
When multiple applications must encrypt/decrypt the same data, it stands to reason, that they must share a common key, algorithm, data-structures, etc. before they can read each others' encrypted content (cphertext). When one of those programs is Java, you can take advantage of the many capabilities of the JCE in any platform that supports a JVM to make this possible.
About 10 years ago, I remember Oracle had committed to enabling the capability to run a JVM inside the RDBMS so stored-procedures could use it. If they have enabled this, then you may be in luck: you can create a single Java library that can be used inside/outside the RDBMS to perform the cryptographic functions you want. You will also be able to choose the algorithm you want with the key-size you want rather than be restricted to what Oracle provides within its own encryption engine in the database.
Another approach you can take is to off-load the entire cryptographic processing to an external "black-box" over a web-service, and just call that web-service to encrypt/decrypt what you want from any platform of your choice: C/C++/C#/Java/PHP/Ruby/RPG, etc. This not only has the benefit of externalizing the entire cryptographic processing, but also addressing many key-management issues which are non-trivial to solve. (What I don't know is if Oracle stored-procedures can call web-services from inside the database, although I don't see why it shouldn't be possible; if Oracle doesn't support it, then it should still be possible to have the stored-procedures call an external Java library that can make the web-service request and return the response to the stored-procedure for further processing).
In the final analysis, what you seek is technically possible; you need to investigate the capabilities of the stored-procedures engine and review them against your business, operational, technical and security requirements before you design the solution. If this is all new to you, retaining an independent expert to advise you will be well worth the money.