It would work perfectly well without Cat, with, as a command line tool, in CGI::Application, as the data source of a standalone CGI service. Anything. This is the best practice way of doing schemas in Cat precisely because the schema should not be tied to the web application. It's entirely reusable this way.

I don't have time right now to cook up a full tutorial so this isn't complete (a better example would have working code to do it all in SQLite or something) but it's pretty close. I know DBIC can be hard to hash out but the info is out there and the list is friendly as long as you don't ask about "DBIx" when MST's around. :)

1. You need a user table, a role table, and user_role join table to work with this particular approach; there are other ways to do it but this is flexible and what I'd call best practice (compared to a role column in the user table for example which is easy to implement but has huge limitations you'll hit quickly and require spaghetti to solve).
2. The minimum necessary table layout is approximately-

   CREATE TABLE `user` (
     `id` int unsigned NOT NULL auto_increment,
     `username` VARCHAR(100) NOT NULL,
     PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   CREATE TABLE `role` (
     `id` int unsigned NOT NULL auto_increment,
     `name` VARCHAR(25) NOT NULL UNIQUE,
     PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   CREATE TABLE `user_role` (
     `user` int unsigned NOT NULL,
     `role` int unsigned NOT NULL,
     PRIMARY KEY  (`user`,`role`),
     FOREIGN KEY (`user`) REFERENCES user(id),
     FOREIGN KEY (`role`) REFERENCES role(id)
   ) ENGINE=InnoDB;
   [download]

3. Create your DBIC schema. There is a good stub here: Re: Switch from DBI to DBIx::Class: thoughts?
4. In your DBIC schema files, if you followed the above example, you will find these classes/pms were created-

   MyApp::Schema::Result::User
   MyApp::Schema::Result::Role
   MyApp::Schema::Result::UserRole
   [download]

5. Add to MyApp/Schema/Result/User.pm beneath the "DO NOT MODIFY this" line. If you do so, you can regenerate your schema files automatically while preserving any code you've added-

   # Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-02-28 19:1
   +4:54
   # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:iS0oJWk28gjL7QD50cdRqQ
   
   no warnings "uninitialized";
   use Scalar::Util "blessed";
   use List::Util "first";
   
   __PACKAGE__->many_to_many( roles => "user_roles", "role" );
   
   sub has_role : method {
       my $self = shift;
       my $role = shift || return;
   
       if ( blessed $role )
       {
           return first { $role->id == $_->id } $self->roles;
       }
       elsif ( $role =~ /\A\d+\z/ )
       {
           return first { $role == $_->id } $self->roles;
       }
       else
       {
           return first { $role eq $_->name } $self->roles;
       }
       return;
   }
   [download]

6. (Optional, I think, but makes some things much more DWIM) Add to MyApp/Schema/Result/Role.pm beneath the "DO NOT MODIFY this" line-

   use overload '""' => sub {
       return +shift->name;
   }, fallback => 1;
   [download]

7. Now, you connect the schema like you would anywhere. Catalyst uses its Model space to abstract this a bit. Since we're not in Cat, we'll do it manually-

   my $attr = { RaiseError => 1,
                AutoCommit => 1,
                ChopBlanks => 1,
               };
   
   my $schema = MyApp::Schema->connect
       (
        "dbi:mysql:db_name",
        $user,
        $pass,
        $attr
       );
   
   # Just grab one-
   my $user = $schema->resultset("User")
           ->search({},{ order_by => 'RAND()' })->first;
   
   print
      "User ", $user->username,
       " has these roles: ",
        join(", ", $user->roles) || "none!", "\n";
   [download]

And you're good to go. Wherever. :)