While the CAA checker algorithm in RFC 8659 does walk up the original FQDN tree, it does stop at the first found CAA record. In addition, it does assume that resolvers follow CNAMEs during existance checking of a CAA record.
This creates a potential problem:
If www.example.com is CNAME'd to evil.com, the operator of evil.com can set a malicious CAA record on evil.com, which will be found before the CAA of example.com is checked. CAA checking stops on the first result. A CAA record on www.example.com is impossible due to the CNAME. Therefore, in this case a restriction via CAA appears to be indeed impossible and @rmbolger is right. (IIRC, this used to be catched by the old RFC 6844 algorithm, but the newer one doesn't seem to support this use case anymore)
(If there is no CNAME on www.example.com all of this does not apply)
The specifics really depend on your exact setup, which CAA record is where. A domain can have much more than one CAA record and they allow for various settings.
In particular, there's an extension to the CAA specification (RFC 8657), which allows for restrictions by ACME accounts. Let's Encrypt does not currently support this, but has plans to do so. With this feature, you can forbid anyone but yourself from issuing wildcards.
Without the extension, you can still play around with various combinations of CAA records, which might allow what you are looking for. In particular with a CAA record set only on www.example.com it does not apply to example.com.
Or (provided I'm understanding this right) if the CNAME doesn't point somewhere that a "rogue" CAA record might get set up that short-circuits the CAA at the original domain.
i.e. There no ban on having a CNAME, it just can't/shouldn't point to somewhere where a hostile actor can setup a short-circuit CAA record themselves. (Point to somewhere we control, rather than evil.com)