Generally Speaking:
The reason noise is bad is because it is a distraction from the image. As 'distraction' is subjective, each image stands on its own and the personal observation of the viewer, in regards to noise. Like most elements in photography, there is no simple answer, no Black or White decision, but gray with, again, the answer dependant upon the individual viewer and the actual image. A photo with a high level of Image Impact can take more noise and still hold the viewer's focus than a photo with less Image Impact. i.e.- Nick Ut's image of the young screaming child running naked up the dirt lane after being hit with napalm in Vietnam, is grainy and not sharply focus, but the capture of the raw emotions which overflow from that photograph engulfs the viewer and holds their attention. Conversely, the photos of Edward Weston's Bell Peppers, could not take such a grainy hit as Ut's photo. The Image Impact is much more subtle in the Weston shot and requires a different presentation. It is the lighting and the tonal range which makes the image successful in Weston's photo. It is the intensity of the subject matter which makes the Ut photo successful.
Noise is not a one-size fits all matter. Personally, I think that limiting yourself with restrictions prior to and without considering your subject matter is not very wise.
On the technical side, in low light situations, careful metering and low light shooting techniques go a long way to overcoming equipment inadequacies.
Good Luck and Good Shooting,
Gary