First Perl DB integration project

ajt has asked for the wisdom of the Perl Monks concerning the following question:

I have a requirment to restrict access to parts of a web site. Life is not easy, so I can't just use Apache's built in access control systems, a flat-file password list and be done with it. I'm sure this scale of problem that other people have faced, so I beg for insights from more learned monks...

First task, is to extract from SAP a list of customer IDs and passwords. I can get SAP to split them out as a text file, or suck them out with Perl. Either way this happens inside our intranet, and not on the box doing the actual serving.

Second task, now I have the base list of customers, I need to allow them to add usernames and passwords. I have MySQL on a hosted BSD box running Apache, Perl (? mod_Perl) and mod_PHP. We need to allow each site (one customer ID) to give it's staff as few or as many usename and password pairs as they want. We don't want the customers to use their customer ID and password to view the site, as that get's messy.

Finally I need to purge or suspend any usernames/password pairs that no longer have a matching valid customer ID in SAP. Our customers are large oganisations, and we don't have much turn over, so we can do this in batch mode once or twice per week.

I see the following bits:

Apache, MySQL authentication integration. Not a Perl issue, but I need to manage it on the remote box, and allow users to manage their own passwords and usernames via a web interface, and I think this is a canidtate for Perl.
Generate the MySQL database to start with and keep it in sync with the SAP master. This is an ideal candidate for Perl.
Various scripts will run on the server, and tie into the authentication system, but that happens after Apache has done it's bit, so I think that's easy to deal with.

Now I've done Apache and Perl/CGI-BIN for a few years now, but never on a scale where a DBMS was used. Our in-house SAP team is good with SQL, but not Perl. Where do I start?

The DBI Book as reviewed here Programming the Perl DBI.
I think merlyn has an example of a usename password script here . It may need some modification, is it a good place to start?
What traps should look for and try to avoid, I can change my project if I need to now, I have a clean piece of paper, other than the SAP system, which I have to live with.

What I see as the simplist approach is to password protect the "password changer" with the customer ID/password, that way only the customer's admin can use it.

Password restrict the rest of the site, using the passwords/usernames controled by the site passwords/usernames. The customer ID will not work here to prevent them from using the same username and password for everything.

Keep the DB on the remote host in sync with the SAP master on the intranet.

As ever, humble thanks in advance.

Edit Masem & others 2001-10-03, fixed html that was breaking on SoPW page (near the ref to merlyn's column)

Comment on First Perl DB integration project

Replies are listed 'Best First'.
Re: First Perl DB integration project by CubicSpline (Friar) on Oct 03, 2001 at 16:37 UTC
It seems that you've thought this through enough that your ideas sound solid enough to me. Interacting with DBMS is no big deal in Perl, especially using DBI. Indeed Programming the Perl DBI is a fantastic book for a Perl user who is wanting/needing to learn how to interact with a database. I used this book on my first database/perl project and it was easy to use as a learning tool and as a reference later on. As far as traps go, the only thing I would take time to consider is the use of MySQL as your database of choice. I personally use MySQL for home projects, but it does have some features that are lacking in other production-quality DBMSs. In general, people recommend Postgres as a more stable and more powerful database system. Depending on how many users, how frequently they'll be interacting with the database, etc.... you may want to look into using Postgres instead, or at least benchmarking the too with some sort of simulation to find out which one better suites your needs. Cheers, ~CS	[reply]
Re: First Perl DB integration project by perrin (Chancellor) on Oct 03, 2001 at 19:45 UTC
It sounds like you're planning to do data munging in Perl and then export that data to MySQL in a format that is supported by an existing auth module in C. If you are running mod_perl, you might want to consider using one of the auth modules in Perl, since they'll be easier for you to hack to fit your own needs. There's a list of them here. Of course, you will need to learn how to work with mod_perl for this, and that might be more than you want to deal with right now.	[reply]
Re: First Perl DB integration project by sheridan3003 (Beadle) on Oct 03, 2001 at 19:00 UTC
I agree. It sounds like you have a good handle on the approach. You should just need to install the DBD::Mysql and go to town with updates to it. Have your SAP team review your SQL if you are not that familiar with it. Over all this is a good project to show what Perl can do with database access as well as the other things it can do. Your comments after the where do I start would be a good place to start go through that process jot down any real problems you run into and bring them up here for assistance.	[reply]