Water-based stains offer virtually no protection from the elements, including rain and sunlight. Even an interior surface treated with a water-based stain should be coated with some type of protective sealer, such as varnish or polyurethane.
An oil-based stain is going to offer considerably more protection for your wood than a water-based stain, but only against moisture. When stain comes in contact with wood, the wood's pores absorb the stain, which is why you usually have to sand wood to get rid of old stain. When treated with oil-based stain, the wood takes on the oil's properties and becomes water-resistant. Apart from water resistance, oil-based stains do little else to protect the wood from UV rays and minor damage.
Tung oil and linseed oil are commonly used to stain wood while waterproofing it. Tung oil tends to give wood a much darker appearance, while linseed oil is better for lighter colored wood grains, such as maple. As with oil-based stains, these natural oils only offer protection from moisture -- they do not offer protection from UV rays or create a protective shell to defend against scratches or other minor damage.
If you really want to weatherproof a wood surface, you must apply an wood sealer, such as polyurethane or varnish, designed for use with exterior surfaces. Most exterior products contain additives that protect against UV rays, which can be just as harmful to wood and wood finishes as moisture. Exterior wood sealers can be water- or oil-based; the latter will turn an amber color when it dries, so you might want to stick with water-based products if this is not desirable.