What does this character do?
A spy can take many forms, but all of those forms are based around a central conceit. There is another faction out there with information, and the spy's job is to find that and take it. Sometimes that means being a quiet mole in the organization who easily evades notice, sometimes that means seduction or interrogation, and sometimes that means a series of stealthy assassinations during a break-in. But no matter what, the spy is getting some knowledge that she shouldn't have without being caught.
Spies as player characters can take two forms. The first is a spy working for the same group as the other player characters, such as an Illuminati agent in The Secret World spying on the Dragon. The other is a spy for another group -- a Blood Elf in World of Warcraft who is secretly working with the Kirin Tor, or a Klingon officer in Star Trek Online who is serving as a mole for the Empire. Your actual in-game faction is the group that you're spying on.
Is one better than the other? Well, both kind of put you in a bit of a bind. No matter what, you'll have a hard time in many games interacting with both the faction you're working for and the faction you're spying on, especially if one of them is an NPC faction. Spying on the faction you're playing makes you mildly villainous no matter what; spying on another faction means that your spying actions are a bit more detached from immediate play. Choose carefully.
What does this profession provide for roleplaying?
In a very practical sense, spies make for great explanations of why a character has vanished for a while. If you're playing a spy against your in-game faction, then you had to go home and report in. If you're playing a spy for your in-game faction, you were out on another mission to obtain some useful bit of information. Either way, you have a reason in hand and a chance to create another plot based on what happened while you were away.
But even if you're just talking about character benefits, a spy provides your character with a lot of opportunities to ask and raise questions. Double agents spend a lot of time working with the other side, sometimes even forming friendships and falling in love along the way. Imagine a character who starts off as an idealist spying against people she believes to be evil incarnate. As time goes by, she slowly sees the evil in her own home and the good in her supposed enemies, until eventually she isn't sure whom to trust. And confessing what she's doing to either side of the fence is equally dangerous.
What sort of characters work best in this role?
A real spy is akin to a magician in many ways; she works by misdirection. Her goal is to either avoid attention or attract the wrong kind of attention, to keep everyone looking in one direction while she's working in another. Sometimes that means being quiet, unobtrusive, and wholly forgettable. That makes for boring roleplaying, though, so more often your spy will need to focus on deflecting attention, quietly earning trust, and playing a long game of manipulation.
Spies also tend to be paranoid to some extent. This is both a trait of a job and a benefit to being a successful spy; it's not really paranoia when everyone is out to kill you, after all. Spies will probably not trust what other people say, will suspect and question even simple statements, and will be prone to over-analysis.
A good spy will have a way to work herself into an organization, the skills to stay hidden via misdirection, and the ability to sniff out when that's going south. And if you want to remove one of those traits to specifically make a bad spy, that works as well.
What should I keep in mind?
Even if your spy's status is an open OOC secret, playing a spy means playing a character who is explicitly betraying someone. A spy has people on both sides who trust her, and one of those sides should not. Be aware of that and keep in mind that you might have some complicated in-character discussions that can spill out of character if you aren't careful.
Also remember that a spy means working for someone. Infiltrating an organization just for the heck of it isn't really spying; your spy will have a superior who is probably expecting reports and updates. That means that your character has priorities to observe and obligations to fulfill, providing an added pressure for those who are reluctant spies in the first place. There's material for miles when you put together the Spy profession with the Trapped archetype, for example.
Feedback is welcome in the comments below or via mail to firstname.lastname@example.org. Next week, I'm going to talk about what it takes to make a good roleplaying guild -- which might not be as complicated as you think.
Every Friday, Eliot Lefebvre fills a column up with excellent advice on investing money, writing award-winning novels, and being elected to public office. Then he removes all of that, and you're left with Storyboard, which focuses on roleplaying in MMOs. It won't help you get elected, but it will help you pretend you did.