Does an SSL certificate work on CNAME'd urls?
No, I fear the previous answer is wrong. If you stick to giving users subdomains then your wildcard certificate will work, but if you allow users to map custom domains to their subdomains with CNAMEs, then they will need separate certificates for each custom domain, and separate public IP addresses as well.
Yes, CNAMEs work if the CNAME is within the wildcard's match pattern.
If your cert is for *.example.com, no you can't use a CNAME like joe.domain.com