Let's break down a .mobileprovision file piece-by-piece:

1 - The app ID prefix

    <key>ApplicationIdentifierPrefix</key>
    <array>
        <string>X00X0X000XX</string>
    </array>

2 - When the profile was created and when it expires

    <key>CreationDate</key>
    <date>2012-06-27T05:02:35Z</date>

3 - The list of certificate(s) that can sign code - this is the public key corresponding to the private key of your developer certificate. For a team profile, this has all your team member's public keys. For Ad-hoc or App Store, this may have a more restricted list of developer certs to control which devs can actually release builds into the wild.

    <key>DeveloperCertificates</key>
    <array>
        <data>Base-64 certificate public key goes here</data>
    </array>

4 - A list of entitlements, such as game center info, keychain info so apps can share keychains, iCloud ubiquity identifier, etc. In theory I assume this could be used to grant all kinds of permissions to bypass the sandbox. A non-wildcard profile specifies the explicit app identifier here; the device will refuse to honor certain entitlements if they are wildcards, that's why you need app-specific profiles if you use certain features.

    <key>Entitlements</key>
    <dict>
        <key>application-identifier</key>
        <string>X00X0X000XX.com.obsolete-software.myapp</string>
        <key>com.apple.developer.ubiquity-container-identifiers</key>
        <array>
            <string>X00X0X000XX.*</string>
        </array>
        <key>com.apple.developer.ubiquity-kvstore-identifier</key>
        <string>X00X0X000XX.*</string>
        <key>get-task-allow</key>
        <true/>
        <key>keychain-access-groups</key>
        <array>
            <string>X00X0X000XX.*</string>
        </array>
    </dict>

5 - Non-App Store profiles also contain a list of device UDIDs.

    <key>ProvisionedDevices</key>
    <array>
        <string>*device UDIDs go here*</string>
    </array>

6 - Then there's the binary container that wraps the plist and includes Apple's digital signature. The entire profile is signed by Apple using their private key. Every device ships with Apple's certificates in the root trusted store and will refuse to load profiles not signed by Apple. The provisioning portal, enterprise accounts, et al is just a way for Apple to manage what goes into the profiles and whether or not they want to sign the resulting .mobileprovision file. You can see some of the certificate strings hiding inside the binary data:

Apple Inc.1&0$UApple Certification Authority1-0+U$
Apple iPhone Certification Authority0
Apple Inc.1503U,Apple iPhone OS Provisioning Profile Signing0Ç
"0

The latest Xcode has fixed one of the biggest disconnects with Provisioning Profiles. It's a subtle thing, but I think it sent a lot of developers off in the wrong direction. Prior to Xcode 5, you would select a provisioning profile in your build settings but that profile was never actually involved in the build process at all. Xcode would take the profile you selected then look in your keychain certificates and try to find a certificate with a private key that matches one of the public keys listed in the profile (Teams in Organizer). When it can't, that's the dreaded "Valid signing identity not found" error.

In the new world, you just select the team directly on the project general view and Xcode will use the certificate that team has listed for you. (Many people aren't aware that your Apple ID can be a member of multiple teams).

When running on a device, Xcode checks for provisioning profiles that match the app Bundle ID you are using, that you have a signing certificate for, and that have your test device's UDID. The overall experience is much improved.

Assuming it finds the certificate it signs the executable with the certificate private key. This is bog-standard Mach-O LC_CODE_SIGNATURE executable signing with the codesign utility. That proves you did the build and the executable wasn't tampered with. Apple signs everything they ship so you can view the signature information on your Mac if you like

MBP# codesign -vvv -d /bin/ls

Executable=/bin/ls
Identifier=com.apple.ls
Format=Mach-O thin (x86_64)
CodeDirectory v=20100 size=261 flags=0x0(none) hashes=8+2 location=embedded
Hash type=sha1 size=20
CDHash=515901e3f11e55b861ec9a0905979bfbabc8b343
Signature size=4097
Authority=Software Signing
Authority=Apple Code Signing Certification Authority
Authority=Apple Root CA
Info.plist=not bound
Sealed Resources=none
Internal requirements count=2 size=180

That's it as far as your build goes. It's your certificate private key that protects from someone making malware builds with your name on them.

When the device installs or runs the code, it looks through installed profiles to find one specifying the matching public key and either a matching app ID or wildcard to validate the executable. It won't accept profiles not signed by Apple, expired profiles, or profiles that don't include its own UDID. The signature is verified to make sure the executable hasn't been corrupted. The sandbox also enforces the entitlements specified.

The exception is the App Store, where I believe Apple's servers verify the uploaded build, then strip and re-sign with Apple's certificate but I haven't confirmed that. It makes sense though, because you wouldn't want to require all devices to check for revoked developer certificates or have binaries expire because the developer's certificate expired.

So the short version is if you have the wildcard profile installed you can build apps all day long without doing anything else, so long as you don't want to share keychains, use iCloud/Game Center, etc. The profile only needs to be updated on new devices when that device has its UDID added or on all devices when a new team member/build server is added to the mix. You should now be able to guess why that is.