Let me get a little bit into the weeds of how color vision and color blindness work to provide a better understanding of how color blindness could affect art ability and success. "Color blindness" actually covers a lot of different conditions characterized by a reduced or altered perception of at least some colors, but I'll stick with the most prevalent condition for this answer.
Color "blindness" is a bit of a misnomer because while your eyes have reduced ability to differentiate certain colors, you still see the colors. For problem colors, you basically see an "optical illusion" of color.
Your visual experience of the world is images created in your brain. Your eyes provide data, but the images produced by your brain aren't one-for-one matches with what your eyes see. Much of it is interpretation.
Normal color vision
Normal eyes have three types of cones (the receptors for color). Each type is sensitive to a distribution of colors (light wavelengths) with the most sensitivity being to one of the primary colors (red, green, or blue). These distributions heavily overlap, so most color wavelengths trigger activity in at least two kinds of cones, some all three.
Image courtesy The Color-Sensitive Cones
Your brain has an idea of what all the colors are supposed to look like. It determines what a color is by comparing how each different type of cone reacts to it, then assigns that color. The color you see is the color your brain tells you it is. With normal vision, that color interpretation is very reliable. If something looks green, under normal conditions you can count on the fact that it is green.
Take differentiating red from green, for example. The peak sensitivity for red cones is mostly yellow light. They are triggered equally by greenish light to the left of the peak and reddish light to the right. The green cones are most sensitive to greenish light. They also react to reddish light, but less so than the red cones. Blue cones have a little sensitivity to greenish light. With normal vision, comparing how the three types of cones respond tells your brain whether the light is reddish or greenish.
Color blind vision
With color blindness, your eyes have a deficiency of cones for one of the primary colors. The process still works the same way, but your brain has to rely on the activity of two types of cones instead of three. In the red-green example, above, if you remove the "green" curve, there are still some clues. For much of the green range, the blue cones have a little activity to compare against. If there's no blue cone activity, a color that's off-peak for the red cones is probably reddish.
Your brain can adapt to some extent to use the reduced data available to make color interpretations from the cone activity in your eyes. But as you can see in the image, there are ranges of color where that would leave your brain relying almost entirely on data from one type of cone to differentiate two primary colors that the cone is equally sensitive to (i.e., a certain level of activity triggered in the cone could have come from either greenish or reddish light).
Your brain is wired to assign a color, it has to assign something, and it has to do it in real time. So it picks one, and that's the color it looks like. The color you perceive isn't necessarily the color that enters your eyes, it's the color resulting after the Jean-Luc Picard cells in your brain say, "Make it so." (Sorry if any readers are unfamiliar with the Star Trek reference.)
This color selection is essentially a guess. If you look again at the same color, your brain will make a guess and assign a color, but that guess may not be the same.
How color blindness manifests
With mild color blindness, strong colors may not be much affected. In general, the way color blindness manifests is this. Say you are red-green color blind and look at a single color in your problem range. Depending on the surrounding context, your brain might interpret it as red and it will look red. With a different surrounding context, or even just looking at it again, your brain might interpret it as green and it will look green. When it looks red, it unequivocally looks red. When it looks green, it unequivocally looks green.
If you look at multiple problem colors adjacent to each other, like a color blindness test image, your eyes can't reliably discriminate the different colors. Your brain assigns color guesses. So some components look like the right or wrong color guess, and some components may look like a hue that is a combination of the problem colors. These color assignments are essentially random. So you can see the colored components, but the embedded shape doesn't survive the random color assignments.
Color blindness glasses
The special glasses mentioned in A. Staffelbach's answer work by filtering out narrow ranges of the problem colors. By eliminating those colors, your brain is better able to guess colors from the remaining clues and you can more reliably differentiate colors close to the problem colors. A side effect is that what you see is a bit like the color enhancement setting on a monitor or TV that makes colors more saturated and contrasty. The result is an experience that is "better" but "less accurate" (relative to actual, but not relative to your own baseline).
Effect on art
If you apply these characteristics to art, you can understand how your art would be affected.
It will be difficult to work with pale colors in your problem range. You will have trouble differentiating the detail you create in those areas. In order for you to see the detail, you may need to use higher contrast, which might not look accurate for the subject matter.
You will probably accurately recognize colors individually in their original containers or applicators. But once you mix your problem colors and apply them (particularly light colors), you will be subject to color confusion. Without some mechanism to help differentiate applied colors, you have the risk of creating areas that look correct to you, but would look miscolored to non-color-blind people. If you use a one-step process that lets you apply colors in essentially their final form, that would minimize this risk.
The biggest problem will be in recognizing when and how colors are off (is it really a little too red or a little too green). You may recognize that the color isn't right, but won't reliably be able to tell what the error is. A tool that might help with that is a color analyzer. You'll need a good white light source, a sensor or calibrated camera, and some software (or a non-color-blind partner who can look at it and tell you).
The specialized glasses may help with some of these problems, but they can't accurately correct color perception for the problem colors (they work by filtering out some of these colors). They will improve your experience of color and your ability to differentiate colors, but you won't perceive the problem colors the same as a non-color-blind person. So what you create may look miscolored to them in areas with those colors.
If you focus on art that involves a lot of use of your problem colors, it will be a challenge for you, which may diminish how rewarding you find it. If you focus on art where those colors rarely come into play, color blindness could be largely irrelevant.
