As for the retcon in Discovery, I'm rather fond of the headcanon that before the end Discovery Season 2, Section 31 and Starfleet Intelligence were one and the same, but after the events of the last episode they determined they should move certain Starfleet Intelligence activities off the books. It's still secretive up to the 24th century, but by the 25th century it's become a bit of an open secret.
I think Section 31 in DS9 was excellent because it shows the complete suspension of morals/Federation principles for the sake of preserving the Federation and poses the question “do we betray our morals in order to preserve them?”