Yes, there is a way to allow users to access a DBMS with custom permissions, but you'll have to be very careful about security. The steps might go like this:

• User arrives at site, clicks "log me in to database"
• User is sent to (preferably) an HTTPS URL where they are presented with username and password; they then enter in their DB user name and password
• session for the user is created (perhaps in DB, in a table readable by CGI script); session ID can be stored on the client-side in, say a cookie.
• on each subsequent request, the user's cookie is read, the session info looked up, and used to get a connection to the database
• after the user has explicitly clicked 'log out' or hasn't made a request for a timeout period (say, 5 minutes or whatever makes sense), the session is deleted from the DB.

Now, that won't get you the caching goodness of Apache::DBI, and you'll have to create a new db handle on each subsequent request. Myself, I'm just too lazy to read up on whether Apache::DBI is able to cache many different kinds of handles (different users/passwords); but I wouldn't be surprised if it did.

Update maybe it will: gratuitous document quoting =>

[when configured appropriately, Apache] looks if the 
environment variable GATEWAY_INTERFACE starts with 
'CGI-Perl' and if the module Apache::DBI has been loaded. 
In this case every connect request will be forwarded to the
Apache::DBI module. This looks if a database handle from a
previous connect request is already stored and if this 
handle is still valid using the ping method. If these two 
conditions are fulfilled it just returns the database 
handle. The parameters defining the connection have to be 
exactly the same, including the connect attributes ! 
If there is no appropriate database handle or if the 
ping method fails, a new connection is established and 
the handle is stored for later re-use
[download]

so, apparently, yes. HTH