Dubai has a burgeoning nightlife scene and even formerly straitlaced Abu Dhabi has loosened up and tried to catch up. Alcohol is available in alcohol stores, 5-star hotel restaurants and bars in all emirates except Sharjah, where you can only drink in your home or in an expat hangout called the Sharjah Wanderers. As a tourist, you are permitted to buy alcohol in bars and restaurants to drink there. If you are a resident, you're supposed to have a alcohol license never asked for in bars which also allows you to buy alcohol at alcohol stores they do check.
During Ramadan, no alcohol is served during daylight fasting hours. Dubai and Abu Dhabi permit bars to serve alcohol at night, but bands stop playing, background music is off or quiet, no dancing is allowed and nightclubs are usually closed. On certain holy days in the Islamic calendar, no alcohol is served publicly in any of the UAE.
Do not under any circumstance drink and drive in the UAE. If by chance you are in an accident, this becomes a card for going directly to jail — especially during Ramadan. Taxis are widely available if you have been drinking and are a much safer and wiser option given the insane driving habits in the region.