The difference is that people have a very closely protected idea of 'normal' and seek to preserve that beyond all else. Ameliorating disease is acceptable to re-attain normal. Enhancement above 'normal' is just as aberrant, if not more so, as permitting disease to spread unchecked. This is woven into the fabric of our society and always has been. The FDA in the US, for example, is tasked with regulating things which claim to "treat, cure, or prevent disease." They do not permit anything which is intended to enhance or improve a person beyond 'normal'. Were a pill developed tomorrow which gave the taker the ability to lift 500 pounds more with no negative side effects, it would be banned by default.

A good concrete example and disproof of your expectation which starts your second paragraph is piracetam and other members of the racetam family. They're legal in the US, as well as in other parts of the world. They are well supported by research to provide cognitive enhancement. The side effects are minimal. Yet, it's not widely available in the US. It has no way in to a market which is regulated for the purpose of preventing or treating disease, with no provision for enhancement.

There are, make no mistake, substantial and very important issues surrounding the idea of human enhancement, don't misunderstand me. It's just that those serious concerns are not what concern the majority of people. The majority are concerned about preserving a fictional idea of 'normal'.

People come up with a lot of rationalizations for being opposed to artificial self-enhancement. But most don't apply the same standards to "natural" self-enhancement, even when we know that there are severe tradeoffs.

In particular, people overly discount the value of life-seconds. What is the opportunity cost of putting in however many extra hours it would take to practicing chess, in order to account for the benefit of using modafinil? Is that cost outweighed by the small risk that there's some hidden deleterious effect of modafinil which has somehow eluded scientists for thirty years? And even if so, why should we police individual choices of what risks to take in life?

After all, we know that baseball pitchers are at extremely high risk for shoulder injuries. Should we enforce rules that limit practice time, intensity, and technique, so that other pitchers don't have to risk their bodies to compete? Should we put an asterisk next to the names of pitchers who are found to violate those rules, and kick them out of the sport?

That seems, broadly speaking, not to be the kind of choice we've made as a society when it comes to "old-fashioned" self enhancement. Yet we do make that choice when it comes to "artificial" enhancement. I put it to you that the distinction is arbitrary.

Edit: Just one other thing: "mind-altering substance the users of which appear to experience no negative effects and major improvements in their functioning"

That very nearly describes modafinil for most people. (If you're going to say "all people", you've set the bar so high that no drug will ever reach it. Ibuprofen doesn't even meet that bar.)

That would be a huge step in the right direction. I actually think we should do the same thing for athletic competitions.