Check this video out about Infamous 2's user downloadable content built via its level editor: http://www.youtube.com/watch?v=8zp5TUrq_Bg
In that video you see a basic example of what it lets all PS3 users do with Infamous 2: place NPC's, use scripts, activators, static objects, etc. Any user can play these user created levels and distribute them. User created contented is also supported for other PS3 games like LittleBigPlanet and Soccom 4. Not sure about 360 since I don't own one :/
I imagine Sony would have an issue with Bethesda creating a store where the PC user mods are published to the console, but I don't see Sony/Microsoft having a problem with a stripped down CK that Bethesda could implement in the console version of the game. Obviously it would be stripped down and significantly reworked so console users would be able to use it with their controller, but I definitely think something like this would be possible, although I don't think Bethesda would waste their time implementing something like this for Skyrim currently.
I'd also like to point out that Sony has worked with Valve on Steam integration. Portal 2 can be bought through Steam and be played on the PC and PS3 as long as your Steam account and PSN ID are linked. Since Bethesda has put in place a way for mods to be distributed through Steam, I think its *slightly* (very slightly) possible they could work something out with Sony. This could mean all texture modifications are sorted out, but other user created content can be used. Perhaps even a subsection of tried and tested content for console owners (although Valve said not to expect Steam integration with the 360 in reference to Portal 2 even though it was technically possible).


Just kidding of course ..or am I!?