It is self-serving and psychologically abusive to tell a civilian you're a superhero, and in some upside down way, expect them to just take it all in without being stressed to no end when the hero is facing superpowered threats every day. Or, if the civilian cannot take it, they might try to involve themselves, or shoot their mouth off to anyone else in a period of high stress and/or weakness. It serves no purpose for the civilian to know, but that's not the main issue. The main issue is that the superhero's life is dangerous beyond the comprehension of any civilian, and in two well-known examples, when the hero's life was not completely secret, two people were murdered.
As for your cop/Seal Team reference--it does not work that way; I have a long family line at different levels of the military, and the one thing they will tell you up front (which has been understood for generations) is that they do not share their lives/work with family or other civilians, and even after their career comes to a close, they are very selective about what they will say--if anything. They live the textbook definition of separate or dual lives due to the dangerous and sensitive nature of their careers. That, and as said of superheroes, its not the civilians' business to know that which does not concern them.
Above all else, its weak, high-school level type of fan fiction to have everyone in on the hero's secret as if its something anyone can just absorb, roll with it, and there are no consequences.