I always use Dark Souls as an example because it is primarily single player with multiplayer/co op elements, uses p2p (so suffers some of the same issues/drawbacks) but is really well ballanced across PvE and PvP.
I bought Dark Souls games originally to play alone, not knowing what an invasion was. I loved the multiplayer aspects so much that I stuck with the series for far longer than I would have, pouring hours into builds, dropping my sign and helping random Hollow/Ashen travelers whom I shared the world with for but a moment.
It's a great single player game, that is made better for being online.
I bought this game as a single player game, had no idea what interdictions were.. You see where I'm going with this.
This game is made better for being online imo, and it has a solo/private option that literally gives you no drawbacks for choosing to use, yet you also have all the great player interaction that comes along with playing in Open as an option when you want it.
Will you get some immature a holes in every online game, yes, is everyone an immature a hole, far from it. There are loads of great random encounters to be had, and that can include being "greifed".
Because honestly if you subject yourself to a certain level of pressure you will learn how to handle that pressure and greifers are no different, adding another welcomed element.
I used to love being invaded in DS, just to parry the invader into living hell, having em run off behind an enemy, hugging their soapstone to return home.
Now I love (but am admittedly a lot less proficient at), turning the tables on the ones who interdict me.
Nothing comes close to the feeling of banishing an invader in DS, and nothing comes close to taking out someone who thinks they are about to ruin your day in this game.
You don't have those highs without Open/Online, and you can't have highs without lows, it is what it is.