The Mods button is disabled in the MainMenu.lua file. To re-enable this button you will need to navigate to the MainMenu.lua file, open it with a text editor (like TextWrangler) and comment out line 33.
~/Library/Application Support/Steam/SteamApps/common/sid meier's civilization v/assets/UI/FrontEnd/MainMenu.lua
Assuming you use Steam
The line is:
Controls.ModsButton:SetHide( true );
To comment it out simply add two hyphens like this:
-- Controls.ModsButton:SetHide( true );
This should re-enable the Mods button on the Main Menu.
Some mods work and some do not, it seems rather hit or miss. If you have a xxxx.Civ5Mod file use something like Ez7z to expand the file which will create a folder that you can then put in your MODS folder which should be similar to this: (Depending on which version of Civil you have)
~/Library/Application Support/Sid Meier's Civilization 5/MODS/ or
~/Documents/Aspyr/Sid Meier's Civilization 5/MODS/ depending on whether you have changed the setting to use the Library instead of Documents.
NOTE: If you are running OS 10.7 (Lion) then
Users/<your_name>/Library/ is hidden in Finder. Hold the Alt key down and select the Finder's Go menu. You will see Library as one of the options in that menu.
NOTE 2: Currently, every time you download a new patch for the game it overwrites your MainMenu.lua back to default so you will have to comment out line 33 again.