Class::DBI ponderings
We're currently considering shifting our core mod-perl system here at work to use Class::DBI, since many of the classes we have already are pretty much reinventing its wheel, and we currently have the time and space to make a few structural changes before the next development onslaught begins. However, I've run up against an interesting little problem. Currently, our database (mysql) has two users for cgi access - one called nobody, which has read-access only to the database, and one called, well, never you mind, which has full access. Scripts that are just dealing with front-end presentation use the nobody user, whilst the CMS side of the system uses the full-access user. The problem with Class::DBI is that its connections persist across classes, and since we want to do as much caching of DB connections etc as possible, the scheme of having two database users gets a bit tricky. So, two major questions: 1) The old nobody/full-access duality is an old piece of history, and rose out of the general principle that it's best to only have a full-access user connected when necessary. However, it came about when there was very little caching in the system, and has now become a pain where once it wasn't. What do other people do? Just connect using a user with full privileges, regardless of the script's task? I can't see huge security disadvantages in this, particularly as such users are locked down to only take connections from localhost. Still, opinions would be nice. 2) If people do connect with different users dependent on task, how do you keep your cached DB handles straight? I'd thought of subclassing admin-related tasks into separate packages, so that they always get an Object::Foo::Admin-created DB handle, but this seems unwieldy in the extreme. Unless anyone can convince me that it's a bad idea, I think having a single full access user is going to be the most attractive solution here, but opinions welcome.
Re: Class::DBI ponderings
On Fri, 2003-02-07 at 17:34, Simon Batistoni wrote: We're currently considering shifting our core mod-perl system here at work to use Class::DBI, since many of the classes we have already are pretty much reinventing its wheel, and we currently have the time and space to make a few structural changes before the next development onslaught begins. However, I've run up against an interesting little problem. Currently, our database (mysql) has two users for cgi access - one called nobody, which has read-access only to the database, and one called, well, never you mind, which has full access. Scripts that are just dealing with front-end presentation use the nobody user, whilst the CMS side of the system uses the full-access user. The problem with Class::DBI is that its connections persist across classes, and since we want to do as much caching of DB connections etc as possible, the scheme of having two database users gets a bit tricky. So, two major questions: 1) The old nobody/full-access duality is an old piece of history, and rose out of the general principle that it's best to only have a full-access user connected when necessary. However, it came about when there was very little caching in the system, and has now become a pain where once it wasn't. What do other people do? Just connect using a user with full privileges, regardless of the script's task? I can't see huge security disadvantages in this, particularly as such users are locked down to only take connections from localhost. Still, opinions would be nice. 2) If people do connect with different users dependent on task, how do you keep your cached DB handles straight? I'd thought of subclassing admin-related tasks into separate packages, so that they always get an Object::Foo::Admin-created DB handle, but this seems unwieldy in the extreme. Unless anyone can convince me that it's a bad idea, I think having a single full access user is going to be the most attractive solution here, but opinions welcome. I tend to use a single user with read/write access and do the security at the application level. They still don't have full access, just enough to do what they need to do. Assuming you have subclassed Class::DBI and then subclassed again for each of your objects (the recommended way) then you can overload db_Main to return the right handle. Basically db_Main is called every time Class::DBI needs a database connection. I overload it so that I can establish the connection dynamically at run time from a config file but there is no reason why you can't have it look at some variable (e.g. the users id or the attempted action) and return a read only or read/write connection. HTH, Simon.
Re: Class::DBI ponderings
On Fri, Feb 07, 2003 at 05:34:39PM +, Simon Batistoni wrote:
What do other people do? Just connect using a user with full
privileges, regardless of the script's task? I can't see huge security
disadvantages in this, particularly as such users are locked down to
only take connections from localhost. Still, opinions would be nice.
This is what we generally do ... but if you want to keep the two users:
2) If people do connect with different users dependent on task, how do
you keep your cached DB handles straight? I'd thought of subclassing
admin-related tasks into separate packages, so that they always get an
Object::Foo::Admin-created DB handle, but this seems unwieldy in the
extreme.
In your main Class::DBI subclass, which will presumably be the
superclass for each of your 'table' classes, you can override the
db_Main method.
Unfortunately, because of the way Ima::DBI just throws this method into
your namespace, you can't do this directly.
You can create another level of subclassing so that you can properly
override the method:
in file My/DBI.pm
package My::DBI::Base;
use base 'Class::DBI';
__PACKAGE__-set_db();
package My::DBI;
use base My::DBI::Base;
sub db_Main {
my $class = shift;
if ($class-different_privs) {
return Ima::DBI-connect_cached('other connect string');
}
return $class-SUPER::db_Main;
}
1;
Or, you can grab a copy of the method after it's been created, and mess
about with it:
in file My/DBI.pm
package My::DBI;
use base 'Class::DBI';
__PACKAGE__-set_db();
{
no warnings 'redefine';
*db_Orig = \db_Main;
*db_Mail = sub {
my $class = shift;
if ($class-different_privs) {
return Ima::DBI-connect_cached('other connect
string');
}
$class-db_Orig;
}
}
It's kinda tricky, and should really be a lot nicer, but it's fairly
flexible (we've used it to have lots of different databases with the
same schema, and the correct one gets picked depending on certain
circumstances (what user you're logged in as, which vhost you're using,
what the path name is, whatever)...
If you've any more questions, the Class::DBI mailing list has a few
people who've done things like this.
Thanks,
Tony
Re: Class::DBI ponderings
On Fri, Feb 07, 2003 at 08:00:07PM +, Shevek wrote: On reading the code, this is sufficient. Don't call set_db at all. This is pretty close to the architecture I used to use: Each class was responsible for providing an appropriate DB handle on demand. If you don't call set_db, and just use connect_cached, then much of what followed in Tony's mail may be simplified. yes, this is indeed true. I hadn't actually thought of that. I need to put together a good write-up on all the different approaches to this. Tony
