It's almost unbelievable how little anyone knows of this and I can't entirely figure out why. Some small details of the system itself are classified, but not the broad strokes. The Intelligence Community (IC), i.e. the CIA, NSA, NGA, NRO all use the SCI system to compartmentalize top-secret data. Additionally, data can get distribution restrictions, usually FVEY or NOFORN, indicating they can be released to five eyes or to no foreigners at all, regardless of clearance. The compartments can get iffy, but the biggest ones are TK for satellite imagery data and SI for signals intelligence data.

After 9/11, for the most part, this data was shared as widely as possible. The IC networks are all able to connect to each other over JWICS, which is the military's top-secret network. Data only classified at the Secret level is shared over SIPRNet, the military's secret network. When you get an account on any agency network, you are issued an IC PKI identity, which includes a client certificate. Every time you attempt to access any system or even just a static site, your client cert is used to grab your clearance attributes from your agency's online database (as in, basic ABAC, something you almost never see on the regular Internet). Access to view is granted based on these attributes that give your clearance level and compartments. Sites have to be deployed in such a manner that they can be redacted element-by-element rather than denying an entire site.

The DoD, on the other hand, uses the SAP system to compartmentalize data. Any data generated by an SAP program is accessible only to members of that program (and to the president, who implicitly has clearance to see literally everything).

Other than the president, you're right that nobody can just pull rank to get clearance. SAP access is granted by the program office of each specific program. SCI compartments are granted by your agency. The clearance itself is granted by a government-wide cross-agency adjudication board based on recommendations from counterintel agents who receive data gathered from the OPM, by investigators who are typically contractors.

Why do I know all this? Because I was a cleared system administrator of a top-secret software environment. This was a IAM user with Administrator policy assigned in AWS, in this case C2S, the CIA's top-secret private version of AWS that is shared and used by the entire IC. It's the same as having Administrator access to an AWS commercial account. It doesn't grant you administrator rights to the entire Internet, just to the resources in your cloud account. Individual applications even still may or may not give you administrator access as well, depending on whether you need it, which has nothing to do with your clearance level. This is regular infosec "does this person actually need to be an admin" considerations. And it includes separation of duties. Typically, admins in one system can't be admins of another unless there is no one else who can do it.

Edward Snowden absolutely did not have access to anywhere near just about everything classified. Nobody has that access. Even the president can't just login into a terminal and grab anything. He can ask and his aides will find someone who can do that, but there is no such terminal that is physically capable of accessing everything, no matter who is logged into it.