There are actually two different types of CSS, there's a persistent version which loads any time any user loads a certain page, and there's a reflected version which only shows up when a user clicks a mal-crafted link. The persistent version is the most dangerous, as it doesn't rely on the user being incredibly stupid. The reflected version is by far the most common, but since it requires the user to click on a malicious link, isn't usually the easiest to exploit.
But either way, they both allow an attacker to display information on a website, when the content didn't originate from that site.
An example of how this could be really bad, would be a script that deletes all of the content from the document body and replaces it with a login screen. Rather than actually logging you in, it submits the username and password you entered to a site the attacker has control over.
Another, less obvious method, would be a script that captures your session cookie and submits that to another site the attacker has control over. If you were logged in to the site, the attacker could use the session cookie to authenticate to the site as you without logging in.
The Content-Security-Policy header prevents any new JavaScript from getting executed. Any inline scripts have to have a matching nonce, or SHA hash in the CSP header. With XSS, an attacker can insert content into the web page, but they can't modify the headers, so this effectively stops all XSS without some additional vulnerability being exploited.
But either way, they both allow an attacker to display information on a website, when the content didn't originate from that site.
An example of how this could be really bad, would be a script that deletes all of the content from the document body and replaces it with a login screen. Rather than actually logging you in, it submits the username and password you entered to a site the attacker has control over.
Another, less obvious method, would be a script that captures your session cookie and submits that to another site the attacker has control over. If you were logged in to the site, the attacker could use the session cookie to authenticate to the site as you without logging in.