There is a whole range of options for different authentication schemes. The usernames and passwords can be stored in flat files (with the standard mod_auth) or in DBM or Berkeley-DB files (with mod_auth_dbm or mod_auth_db, respectively).
For more complex applications, usernames and passwords can be stored in mSQL, Postgres95, or DBI-compatible databases, using mod_auth_msql, mod_auth_pg95, or http://www.osf.org/~dougm/apache/.
If passwords can't be stored in a file or database (perhaps because they are obtained at runtime from another network service), the ftp://ftp.apache.org/apache/dist/contrib/modules/mod_auth_external.c module lets you call an external program to check if the given username and password are valid. If your site uses Kerberos, http://www2.ncsu.edu/ncsu/cc/rddc/projects/mod_auth_kerb/ allows Kerberos-based authentication.
The mod_auth_anon module allows an anonymous FTP-style access to authenticated areas, in which a user gives an anonymous username and a real email address as the password. There are also modules to hold authentication information in cookies and to authenticate against standard /etc/passwd and NIS password services. See the module registry at http://modules.apache.org/.