The word "unconfounded" as used in this context is a bit confusing because when we think of unconfoundedness we often think of unconditional unconfoundedness, e.g., in a randomized experiment where treatment does not depend on any covariates and compliance is perfect. Rubin's use of the word "unconfounded" can be more simply understood as "conditionally unconfounded", i.e., that the treatment assignment is unconfounded conditional on the set of measured covariates.
What does conditional unconfoundedness entail? At its most basic, it is that $P(W|X, Y^1, Y^0) = P(W|X)$, which is a bit different from what you wrote. $Y$ is not involved in a statement about unconfoundedness because it is not observed until after treatment has been received. In contrast, the potential outcomes $Y^1$ and $Y^0$ exist (conceptually) prior to treatment receipt.
When would conditional unconfoundedness be violated? Any time the potential outcomes are associated with the treatment after conditioning on the measured covariates. This occurs whenever there is an open backdoor path between the treatment and outcome. If conditioning on the measured covariates closes all backdoor paths, then treatment assignment is (conditionally) unconfounded.
Note that I talk about treatment assignment being unconfounded; unconfoundedness is a description of the treatment assignment mechanism; it says that whatever mechanism is used to assign treatment is independent of the potential outcomes (and therefore, the outcomes) given the covariates.
If you are used to thinking about missingness, (conditional) unconfoundedness corresponds to missing at random (MAR) for the potential outcomes $Y^1$ and $Y^0$. $Y^1$ is missing for all units with $W \ne 1$, and $Y^0$ is missing for all units with $W \ne 0$. Since our estimand is (often) $E[Y^1]-E[Y^0]$, we need to make an assumption about the missingness of the potential outcomes. When unconfoundedness is true, covariate adjustment methods (e.g., regression, propensity scores, etc.) can be used to recover the treatment effect. Other methods may be available under other assumptions when unconfoundedness is not met.