The security here is the WiFi password anything that connects to LAN gets a LAN vlan tag. but it’s not like anything that connects to any of the SSIDs can get the DHCP lease of some random device on any vlan cuz it got tagged before. Or am I missing something?

That sounds accurate. I have all my devices assigned a specific IP address, based on their MAC address, but that's only per-interface. The other interfaces aren't aware of my assignments for each other.

If I connect my phone to my LAN SSID, it'll get its assigned IP, but if I connect it to the NOT [network of things, no internet access] SSID, it'll get assigned a new address out of the DHCP pool because I haven't assigned it an IP on that interface, until I assign it an IP. But, which VLAN it's connected to will determine which IP its getting, and it still requires me to know the passwords for each SSID.

I believe where you're getting confused is that a some businesses (or homelabs) might use a RADIUS server which will be more like this: ONE_SINGLE_SSID-Broadcast -> Device connects -> RADIUS Server detects account/certificate/MAC -> RADIUS Server assigns interface -> Device connects to VLAN the RADIUS server granted it access to

So, in that scenario, if the ONLY thing that's being used to validate the access is the devices MAC address, just changing the MAC address will effectively grant a completely different level of access with nothing else changing. Most people in a homelab (and even plenty of larger businesses) aren't running the infrastructure to do this though, they're just effectively connecting a VLAN to a port and then that port can only be used to connect to that VLAN. They're doing the same with the WiFi SSIDs where each SSID connects directly to the VLAN.

Usually though, for places that are implementing the RADIUS server, they'll also install a certificate on their devices and the certificate needs to be in place in order to get certain access otherwise the RADIUS server will authorize less permissive access or just won't allow access at all. Or, it'll also need a user to log in to gain additional access.

For wired, the company may also implement port locking where the port will only allow a certain amount of MAC addresses to connect (presumably one unless there is also a VOICE VLAN with a phone being used, in which case it'd be two) where if you change your MAC address (or connect a different device), the port will lock and won't power POE devices and won't allow connectivity until an admin clears the lock. It's possible that they may have multiple VLANs allowed on the port and client side you can change VLANs, but, this isn't typically done on all ports, usually only on trusted ports or ports that need the multiple VLANs (my VM server for instance has access to a port that'll allow multiple VLANs and I just enter the tag I need when I create the VM). This would be similar to your WiFi scenario, the port with the WAP connected to it will have access to multiple VLANs and then those WLANs just connect to the VLAN that they're assigned to.

TL;DR - Typically one wireless SSID connects to one VLAN and if you want to jump to the other VLAN you'd need to connect to the other SSID, so you still have the individual passwords protecting you. On wired, typically VLANs are assigned per port and you can't jump between then, but where they aren't, it should be in a planned way and not just every port having access to every VLAN. Bad implementations exist though, so, anything is possible.