How can I allow users to put their own passphrase driven secret key creation at the time of account creation?

Thanks

I'm not sure if i understood you correctly but here we go.

The keypair of an account have two things, an account public id and a secret seed, this is generated randomly and you can't just override this value by writing your own, however you can still do a system by yourself that is built on top of this and do the thing you want.

Usually Wallets or another programs have this feature and that secret key you are talking about is just a password for protect the real seed.

https://www.stellar.org/developers/guides/concepts/accounts.html