Sing Sing but no See See
As a native of Ossining, I think I am correct in saying that the prison is entirely useless as a tourist destination. To the best of my knowledge, there are no tours of the property available without prior arrangement (for "scared straight"-type adventures, for instance)... There's not much to see from the outside because of the high walls, and they WILL dispatch some angry-looking individuals with guns to "discourage" you from taking any pictures of the outside walls if you want to record your visit. Trust me on this one, I know from personal fairly recent personal experience.
There IS a part of the Caputo Community Center that is dedicated to the prison, but it is not on the prison grounds... It's located on the main road (Rt. 9) that runs through the middle of town. There's also a portion of the Ossining Historical Society Museum dedicated to the prison, but that is further still from the prison proper.
West Point is a lovely place to visit, though. Contrary to what was posted earlier, it is not directly across the river, however. It is probably 45 minutes away, up the river and on the opposite side. WP is definitely worth a visit.