Skip to Main Content
  • Questions

  • encrypt database username password & global variables & log4plsql

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rakesh.

Asked: January 19, 2004 - 11:30 pm UTC

Last updated: February 06, 2023 - 2:55 am UTC

Version: 817

Viewed 1000+ times

You Asked

Hi Tom

q1) Lot of times in our code to connect to backend(oracle) we use JDBC. Either we hardcode the username & password to connect to the database in the java file or pick the same from a properties file.
What i would like to know is there a way to connect to the database & send the password in an encrypted form which will not making any sense to the naked eye.
I read that there is a way we can do this using Oracle Wallet manager but could not find much on the same.
Could you share your views if possible an example on the same

q2)
I have a table(account_details) with 3 cols(deptno, account_name & status)

the status col can hold 2 values (enabled or disabled) for a particular user (account_name)

for a particular dept in the table there are users (account_name) with status as enabled & disabled.

Now we run a process for loading where we require to set all the enabled users for a particular deptno to disabled & when the process completes set them back (only the ones which were enabled status prior to start of this process) back to enabled.

Currently i am using a global variable (package variable) a collection type where i store all the enabled users


in the package i have 2 procedures one for disabling the users & the other for enabling the users

i fill the array values in the stored proc(disable_users) with the user list & set the status to disabled for the same in the table
& then in the stored proc(enable_users) retrieve values from the array & set it back to enabled in the tabel

I am slightly confused what would happen to this glbal package variable (the array i would be using to store the user list)
when the stored procs are called in a multi threaded environment

As global variables will be stored in the memory, do you think it might cause some problems

If yes what approach should i follow to acheive the above functionality.



q3) Recently i read an article on OTN regarding the open source package (LOG4PLSQL) similar on the lines of LOG4J.
The article though does not explain it with proper examples i.e how to use it on our day to day plsql coding

If you have any idea on the same it would be glad if you share your views about the utility of this package in our day to day PLSQL coding
& how it would help us to enhance the quality of database coding





Regards
Rakesh






and Tom said...

q1) passwords are transmitted encrypted already. you need do nothing to encrypt the password, it "happens"

q2) sounds like a data model problem. You need a third state -- "disabled but to be reenabled" perhaps OR another table where you save the keys of the rows to be re-enabled. using global variables leaves you open to "a crash" -- where you will lose their values and be "stuck"

q3) I've never used log4plsql, however a simple google search on it turned up the documentation on sourceforge instantly.....

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

store password in a cryptic form

Rakesh, January 20, 2004 - 11:21 pm UTC

Hi Tom

In a hurry to post the question , i deviated from what i wanted to ask.
Actually my question regarding encrypt password was how do we store the username & password in cryptic form in the application before connecting to the database.

Normally if we have the username & password in the java file it is there for any one to see who can view the .java file
or decompile .class file & view the username & passwrod hardcoded in the .java file.

Another way is to have teh username & password stored in a properties file
But anyone with access to the o/s where the application is running can view the properties file

Regards
Rakesh








Tom Kyte
January 21, 2004 - 6:24 am UTC

for that you would have to ask the java programmers for a "good" solution.

they will have to

a) pick the encryption class they want to use
b) figure out how the heck to do the key management (once the password is encrypted -- what do you do with the encryption key)



I know what he means

RP, December 16, 2005 - 12:30 pm UTC

Hi Tom,

i think you misunderstood what Rakesh is asking.

In Java, to establish the connection pool, the values for the database such as username and password are put in configuration files in clear text.

Rakesh is asking if there is a way round this. I looked at the JDBC guide and Chapter 10 talks about Oracle Wallet. That seems to be the answer but unfortunately, there are no examples of how you can use it in a java app.

Do you know how?

Thanks

RP

Tom Kyte
December 16, 2005 - 1:12 pm UTC

He seemed to be asking very straight forward "how can I store a password in say my code or a properties file encrypted"


You still have the problem with the wallet, somebody now has to provide the password to get to the wallet. It would not be different here.

A reader, March 30, 2006 - 1:45 am UTC


Password encryption in JDBC config file

John, February 03, 2023 - 7:55 am UTC

Hi,

The original question on this thread was posted in 2004. I have the exact same question in 2023 but DB is on Oracle 19c (19.11).

I have this request from Developer.

Currently, on client side, in JDBC config/property file, DB related password is stored in plain text. Application side they are using Apache web server. They want to keep the password in encrypted format for security reasons.

Question -

1. Is it something DBA needs to do anything on DB side for them to keep DB password encrypted Or its for Java developer to figure out on Java side for any feature ?

2. Does implementing database stored procedure using dbms_obfuscation_toolkit will help here in any ways in current version of oracle (19.11). Does storing username/encrypted password in a table and compare... Is this right API to approach it ?

Thank you!
Connor McDonald
February 06, 2023 - 2:55 am UTC

The best way to handle this would be with a wallet, so now passwords are stored in files.

That's how we get applications to talk to our cloud databases

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/connect-jdbc-thin-wallet.html#GUID-BE543CFD-6FB4-4C5B-A2EA-9638EC30900D

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library