In web applications I've done in the past that had a database on the backend and needed user authentication and session management, my general scheme has been something like this: (we'll call the example web app "Acme")

• In the database, in addition to a presumed "users" table (containing user name, user info, auth details (md5 passwd or something), etc), make a "sessions" table. It should probably key on a column called "sessionid" (which would be like a char(40) or something: long enough to hold a base64-encoded secure hash), and have as other columns "username", "ip_addr", "login_time", "last_access_time", etc...
• Make a module called AcmeWeb::Session. Make it a part of your project standards that every CGI must use AcmeWeb::Session;, and that they must, before doing any other processing, do something like my $sess = AcmeWeb::Session->new();.
• AcmeWeb::Session->new()'s logic goes something like this:

    my $dbh = DBI->connect(....);
    if(my $sidcookie = browsercookie('acme_sid')) {
      if(acme_sid is in the sessions table in the database) {
        if(the ipaddr matches current, and last_access_time doesnt indic
  +ate the session timed out already) {
          update_db_last_access_time;
          return $session_object;
        }
      }
    }
    if(username/password cgi parameters exist) {
      check user authentication against database - if it flies, create a
  + new session table entry (hash random numbers for the session id) and
  + then...
      send set-cookie: header to browser with the new acme_sid cookie
      return $session_object
    }
    print_login_page (which submits the above user/pass cgi params)
    exit(0); (if we made it here, don't return control to the calling sc
  +ript)
  [download]

• The new AcmeWeb::Session object, from the calling script's point of view, should contain accessors for the user info (full name, preferences, etc), and should contain an accessor for the database handle that AcmeWeb::Session already opened (in order to do authentication), so that the script can re-use the same handle.

Just a broad overview of the process. I end up doing things considerably differently depending on a lot of project-specific factors, and there's a lot of details being skipped over here, esp wrt to secure programming practices in the auth/session code, but this is the general idea it always seems to boil down to.