If you have a look at the main Apache2 config file,
/etc/apache2/httpd.conf you will see that there are two things that have to be done to allow /Library/WebServer/CGI-Executables contain the cgi files.
First, since it isn't covered by the permissions for the document directory next to it, it will need a "Directory" block to define the permissions for all the files and directories under it. In the case of this directory it allows absolutely nothing to happen. This is why when you pointed your browser at the directory it said permissions denied. Don't worry, the second thing allows cgi files in the directory to be run.
Second, it needs a "ScriptAlias" command that tells the server what URL will be used to point to it so that the server finds the files and allows them to be run.
ScriptAliasMatch ^/cgi-bin/((?!(?i:webobjects)).*$) \
This basically says "after you remove the hostname then any URL that starts "/cgi-bin/" and continues with a piece of text followed by a period followed by another piece of text points to a file with a name matching the last bit in the directory ""/Library/WebServer/CGI-Executables/$1"
If you want to place CGI files in one of your user directories then you will need to make some changes to a different config file. You will find the config file for user shares in /etc/apache2/users with a config file for each user. Here is an example:
Options Indexes MultiViews
Allow from all
To this you will have to add either "ExecCGI" to the "Options" line so that you can have CGI files anywhere in your Sites directory or under or add a new Directory block for your CGI folder. It would look like this :-
Allow from all
Since this is under our DocumentRoot we don't need to use the ScriptAliasMatch.
(The second option is the more secure.)