Are you talking about sharding? Look at "Advanced Apache Rewriting" - at least probably your solution will use a RewriteMap .. although you do say dynamically, I still assume your list of users is static. RewriteMap Apache Documentation should be the way to go - you put your mapping in a text file like so:
Ralf.S.Engelschall rse # Bastard Operator From Hell
Mr.Joe.Average joe # Mr. Average
Then you define this map in the .htaccess/apache-conf:
RewriteMap real-to-user txt:/path/to/file/map.txt
Then you use that mapping in a rewrite rule:
RewriteRule ^/ex/(.*) ${examplemap:$1}
Another way - if you want to check inside that profile.php you mentioned - would be to do the following:
RewriteEngine On
RewriteCond %{HTTP_HOST} !=site.example
RewriteRule ^ http://site.example/profile.php?host=%{HTTP_HOST}&uri=%{REQUEST_URI} [R=301,L]
You should have your setup so that your "catch all"-host has that RewriteRule, but your site.example
-host is seperate and thus doesn't run into a loop. OFC you'll end up with people accessing user1.site.example
and ending up with an address bar filled with site.example/?was=user1.site.tld&uri=/
... If you need something different it's probably possible too though.