As for how to do it, it's pretty simple. That whole base class setup is just a suggestion. You can either not have one at all and decide which database login to use in your class based on some config thing, or you could make Person a base class and then subclass it with MutablePerson and ImmutablePerson, moving the set_db call down to these subclasses.

Uh, you typically restrict your DB to do certain things 'cause you don't want people doing those things. That's why a lot of companies don't allow anything but stored proc's from beign run. You can't run arbitrary sql.

Some that do allow sql, don't usually allow deletes, since you don't want an arbitrary "delete from blah".

It's not paranoia.. it's good security. Or do you not believe in firewalls too.

You don't understand. We're not talking about running arbitrary SQL here, just executing pre-defined method calls on a class. Security is typically enforced either in these method calls (i.e. do you have permission to call this method) or at an even higher level in the web interface. A person who isn't allowed to delete something from the database would simply not be presented with a delete option in the web UI. This makes the extra work to use different database logins redundant, so most web apps do not use multiple database logins for security.

To be fair, I've also found myself staring into space and muttering under my breath, so results may vary...

Try this:

Subclass Class::DBI, adding 2 methods: one that knows how to find out who is using the program (using %ENV?) and one that knows how to generate login credentials as needed. Then use that to call setdb with the right username and password. You might also add triggers that would create an exception if a 'public' user tried to alter the DB.

Then subclass this intermediate class for each table.

Something like:

package MyDB;
use base 'Class::DBI';

__PACKAGE__->set_db('Main', 'dbi:mysql:mydb', &db_login_credentials);

sub db_login_credentials {
  my $group = &who_is_this;
  if ($group eq 'staff') {
    return ('staff', 'password');
  } elsif ($group eq 'public') {
    return ('public', 'password');
  } else {
    die 'who is this person?';
  }
}

sub who_is_this {
  # do whatever it takles to figure out if it's public or 
  # staff, and return either 'staff' or 'public';
}


if (&who_is_this eq 'public') {
  __PACKAGE__->add_trigger(before_create => 
                           sub { die 'not authorized to create'; });
  __PACKAGE__->add_trigger(before_update => 
                           sub { die 'not authorized to update'; });
  __PACKAGE__->add_trigger(before_delete => 
                           sub { die 'not authorized to delete'; });
}
[download]