My problem is this: How do I use Class::DBI to provide both types of access?

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]
This is a common pattern with Class::DBI.

In reply to Re: Different Database Permissions Using Class::DBI by cleverett
in thread Different Database Permissions Using Class::DBI by Wally Hartshorn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.