It definintely reduces the glare of nighttime lights for games played in stadiums, however it doesn't do much during the day.  Also, the only football players who technically 'need' them are defensive backs and receivers, guys whose jobs require that they look up in the sky to catch (or prevent someone else from catching) a thrown pass. 

To be honest, most guys will admit that they wear the black grease just to look a little tougher, a little more intimidating to their opponent. 

You'll never see a lineman sporting black grease under his eyes, unless he's a total poser.