One more trick usable with DBD::mysql is mysql_read_default_group ,an option to have a configuration file with credentials for different applications.

First, you need to create a configuration file, with different [label]s.

# $HOME/.my.cnf

# Here are some general options
[client]
socket=/tmp/mysql.sock
port=3306

# the following are specific to each application

[mysqldump]
user=dumpuser
password=not_my_pwd

[backup]
user=bkpuser
password=not_my_real_one

[usage]
user=simpleguy
password=not_this_one

[readonly]
user=poorguy
password=don_t_try_it

[myapp]
user=specialguy
password=something_different
[download]

Then, in your code you can refer to such labels this way:

my $dbh = DBI->connect("DBI:mysql:test"
    . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
    .';mysql_read_default_group=myapp',
    undef, 
    undef
   ) or die "something went wrong ($DBI::errstr)";
[download]

This code will use the label [myapp] from the file $ENV{HOME}/.my.cnf.

To use a backup application, replace myapp with backup in the above code and your application will use that username and password under [backup].

You can also use this trick to test the same application with different users having different access profiles. (Update. - I mean database users, not O.S. users)

my $profile = shift || 'usage';

my $dbh = DBI->connect("DBI:mysql:test"
    . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
    .";mysql_read_default_group=$profile",
    undef, 
    undef
   ) or die "something went wrong ($DBI::errstr)";
[download]

BTW, the article you were referring to is mine, also published in my blog.

Update - While mysql_read_default_file adds to security, because you won't leave your password hardcoded in your script and you can store it outside the document tree in web applications, using mysql_read_default_group is only a matter of convenience. Using it does not add to security, but just to tidiness.

 _  _ _  _  
(_|| | |(_|><
 _|   

Doesn't the user the app runs under still have to have read access to the file or does the mysql user have to have read access to the file? I'm wondering where the additional safety benefits are coming in over using your own config file.

---

My criteria for good software:

1. Does it work?
2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

Ok this is pretty cool.. I think I need to using something like this. My question.. this hits the disk, so.. performance - If I need to query a table every time a user logs in, etc- this creates an expensive disk operation, right?

If 20 people log in per minute, or if every time a user does something I need to perform some kind of query to the db, then.. won't reading from disk for a db connection sort of slow things?

Maybe I'm missing something here- maybe I should be looking into opening a db connection for a user's session and not closing it somehow ?

Where do you think your database retrieves its data from, thin air? ;-) No, the db reads its data from disk as well, and that's a much more complicated operation than a simple text file read. Luckily (for both cases) the OS will cache often-accessed data in RAM, and therefore the expensive disk operation turns into a simple memory lookup. Which is why database servers can have too little RAM even if only a small percentage of it is being used by the DB process.


A computer is a state machine. Threads are for people who can't program state machines. -- Alan Cox

Now, if I have a text file with the word 'soup' in it. And I have an infinite loop that makes a system call to a script that reads the content into memory and then exits.. . Are you saying at some point the text file will be coming from memory instead of disk ?!

What if it's an infinite loop and it's spaced at a random 1 to 10 seconds appart?