I recommend the following: create a mysql user named "webuser", with a good password. Put the information you need to give to DBI->connect() into a config file (username, password, host, database, etc.). Make sure that this config file is readable ONLY by the user that the web server runs as. If you use the apache web server, you can change this by editing the apache config file. For example, if it's set to 'apache', then any file created by a cgi script will be owned by 'apache' - so make your database config file readable by only the 'apache' user:

chown apache:apache config.ini
chmod 600 config.ini
[download]

To be more specific about the mysql setup, let's say you plan to use a database called 'webstuff'. Run mysql as the root user:

mysql -uroot -p
[download]

create database webstuff;
grant all on webstuff.* to webuser@localhost identified by 'secret_pas
+sword';
[download]

This assumes that your database server is running on the same machine as your web server - else you might have to specify something other that localhost. To be more secure, you could take care to grant only those privileges that you really need, rather than "all". For example, if your web app never has to drop (delete) a table, don't give it the ability to do so. Then, if a bug in a cgi script allows a mysql injection attack, even if someone tries to submit a "drop table" command, it won't actually do anything.

Also, note that webstuff.* means that the privileges apply to every table in that database. If necessary, you specify different privileges on a table-by-table basis. Just because you need to allow updates on 1 or 2 tables, you don't have to allow updates to ALL tables - keep a few "read only" if you need to.

Once created, you can connect like this:

mysql -uwebuser -psecret_password webstuff
[download]

Always make sure you can run your queries manually, from the mysql prompt, before coding them up inside a cgi script. I find this makes debugging easier.

Another trick you can use is to create a "read only" user, a user with limited write privileges, and a user that can do anything (grant all). Then you can write your cgi scripts so that unauthenticated users are in "read only" mode, users that have logged in may have permission to do limited updates, and only a special admin user has permission to do things like drop tables, etc.

I guess the main point is that you NEVER let a cgi script run as the mysql root user. You don't need multiple mysql users in a cgi environment - just one will work fine. You might have hundreds of users on your website, but when they login they each have to authenticate using the same username/password table. NEVER store a password in a cookie. A cookie should be used to store a unique session ID number. Use that to look up a "state" (logged in or not) stored in a session table in your database.

Read the docs for CGI::Application, and CGI::Session. Also, some time ago I started a tutorial on how to use CGI::Application to create a simple login page. It never made it past the "request for comments" stage, but you can still find it here.

Good luck!