As suEXEC only works to become a single user (whosever home directory your script is in), I don't think that's what you want. suEXEC is a solution if each user who wants to use the scripts will locally-install the script (i.e., in their ~/public_html, by default). Then you still want some sort of authentication to ensure that not just anyone who happens to go to ~susan can manipulate susan's files.

Instead, you would need to use a setuid-root executable of some sort. Using sudo is one way to do this.

• Your CGI script gets called by apache. It runs as nobody, or apache, or whatever the system is set up to run under.
• Your "CGI" script calls sudo with the appropriate options. Here you have two choices - call sudo with appropriate options to directly become the other user, or call sudo with appropriate options to become root and call an intermediate script.
• The intermediate script checks the user/password and figures out if they are ok. It then drops privileges to the appropriate user and, optionally, runs the final script (the final phase could be in the same script that drops privileges, but it's sometimes easier to think of as separate scripts).
• The final script actually performs the CGI work.

You need to ensure environment variables are passed through (so don't use the -i option of sudo). And you need to set up the sudoers file to allow this to all work as well, preferably without a password.