Solved

Best Practice MySQL and storing password to another ODBC database

Posted on 2014-01-28
6
788 Views
Last Modified: 2014-02-24
Hi All,

Have a question regarding how best to store an ODBC password for a different database/server in MySQL.  Currently I have an Oracle Database for our Districts student information system.  

I am developing a simplex Perl Program for the Web where Parents will be able to enter in their unique user/password for their student (which I will store in a MySQL Database) and then the Perl program will initiate an ODBC connection to the Oracle student database to prepopulate form data on name, address, dob, etc. that is already in the Oracle system to generate the multitude of Registration forms we have that parents hate filling out because much of the form data across the different forms is redundant.

The goal is for them to enter their user/pass into the system, the system pull data from the Oracle database and put into a simple web form,  modify any data that needs to be and save this new information into a MySQL database and the parents simply print off the forms they modified to bring in during our registration in the fall.

My problem is this.  I can create a MySQL database and create each user for parents, but how SHOULD I store the ODBC password for the Oracle Database which is the keys to the kingdom for reading ALL data in the oracle database?

If I store the Oracle ODBC password in a table in MySQL for the perl program to use to initiate a remote connection to pull data, then any authorized user could in essence remotely connect to the MySQL database and read that password using a command prompt with a simple select statement.  While I know our parents or students won't know how to do that, I would rather not chance it.

Is their a way to specify a specific table that only the local host can read from that table, IE a Perl Script on the server executes the select statement to get the password and is then is used to initiate another ODBC connection to Oracle using the info from that table?
0
Comment
Question by:NoodlesWIU
6 Comments
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 39817449
Hi!

There are several ways to do this.
You can store the username and passw using encryption, SHA or MD5 however there are downside on using just one of the methods. Best way is to combine the methods like it is shown in this blog
http://alias.io/2010/01/store-passwords-safely-with-php-and-mysql/
and here
https://www.owasp.org/index.php/Hashing_Java#Why_add_salt_.3F
http://jackwillk.blogspot.com/2010/08/using-owasp-esapi-php-part-4.html

Hope this helps.

Regards,
    Tomas Helgi
0
 
LVL 76

Expert Comment

by:arnold
ID: 39817529
You should not add direct access from this perl to the oracle DB.  Your perl should use an intermediary connection to a "service page" this way you can maintain security of the Oracle instance while limiting the exposure.

Given you already have login access, which presumably has access mask where each user is granted ......
Is adding a mask that will apply to the parents requires complex modification?
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 39817852
Just some feedback on Tomas's comment - you cannot store the password using hashes like SHA or MD5. Hashes are meant to validate incoming passwords, they cannot be "decrypted", so to speak.

I would not store the password in a database table unless every user has his/her own connection to the Oracle database. Typically, you have a single set of credentials, so there's no real sense to use a database table to store a single record.

When it all comes down, it's going to be difficult to prevent authorized users (those who have access to the filesystem and database) from being able to access the password. You can certainly encrypt it, but the encryption key typically will need to be readable by the script, which means it will be visible to such authorized users.

You -can- limit a user to specific actions on a specific MySQL table:

GRANT SELECT  ON TABLE `database`.`table` TO 'username'@'hostname';

...but an authorized user could use your main connection to query the table.

If you're trying to ensure that local developers and authorized users cannot access the Oracle instance, then the best way is to use arnold's approach in conjunction with a public/private key pair to encrypt the requests, since a separate page would likely mean a separate server (and in turn, a separate filesystem):

1. Server A uses a public key to encrypt a formatted request and sends it to Server B.
2. Server B uses its private key to decrypt the message and run the query against the Oracle DB.
3. Server B formats the response from the Oracle DB into something transmittable (e.g. XML), and returns that response to the initial request.

In this manner, developers on Server A will have their normal access, but they won't be able to see what's happening on Server B, or have access to its filesystem and private key.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 39817858
All that said, that much security is usually overkill. Unless you're dealing with untrustworthy developers and sensitive data, I would just store the credentials in a file outside the web root, use code to read the credentials in as necessary, and connect directly to the Oracle DB from Perl and do the work.

It all depends on your situation.
0
 
LVL 3

Author Comment

by:NoodlesWIU
ID: 39822826
Lots of interesting posts!  I have to say security is relative.  For my situation, I have already salted user/passes for individual users to the MySQL database for authentication.  My problem is how do I use the server to act as a proxy so to speak to say "oh, you've authenticated to my database, now let me use my secret password for the other database to tell server B I'm going to pull specific info for the user who just authenticated to me."

I don't want to store it in a table that the users could theoretically query because it would have to be plain text for the script to query the MySQL record to provide that as the password for the connection to the other database.

But if I can store that password in a text file outside the web directory where only the system could read it, that would be fine for my application and be considered sufficiently secured in my book.

I say security is relative because if my database was storing data in respects to the mating habits of bunnies I could use plain text all day because that info has no real value to anyone else.

But because our master database stores names, addresses, DOB and phone numbers, that would only be used by grade school students parents, I'd like to er on the side of reasonable caution.  I've done that by salting the user passwords, and storing the password on the system outside a directory that the rest of the world can't see but the system can sounds like a "secure enough" solution.

I'm open for feedback on this.  If I'm absolutely nuts please tell me.  I'm here to learn and looking to you guys for some guidance.  =)
0
 
LVL 76

Expert Comment

by:arnold
ID: 39822908
Why not let the Oracle side periodically access the mysql database as a linked server and draw data in/populate responses.
Async versus syncrounous setup.

Parent interacts with mysql creates a request.
SP on ORACLE queries/process prior approved tasks. Gathers prior approved data. updates the mysql database.

This way security is maintained.

oracle can have MYSQL as a linked server.  You possibly can do the reverse using ODBCTAB, but I believe that will/could potentially expose/compromise your security.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now