This introductory snippet really ought to explain everything:
Ex-Insomniac programmer Nathan Fouts created one of the standout Xbox Live Community Games for Xbox 360 in 2D side-scrolling shooter Weapon Of Choice, and explains just how in this in-depth Gamasutra postmortem.
Awesome, isn't it? Now, everyone knows about mods, but homebrewing isn't quite the same since you don't actually have a game to work off of. You're on your own. But the things that can be done!
Its a return to the "old days" when folks could make their own games. Granted, programming isn't the same now as it is then - I don't my practically non-existant BASIC skill will cut it. But I - and maybe more than a few other folks here - remember days long ago attempting to make my own games for the computer... trying to plan it out, writing it, testing and debugging, etc. I never did anything spectacular, but I did make a few mini-adventure/RPG games. Cheesy, lame, but they were mine!
While there are things like Little Big Planet and Kodu out there, to me they're just a start. I'm really looking forward to seeing what else comes out :)