Thieves' Tools can be used to unlock doors and containers and disarm traps. Lockpicks are used for opening locks, and Probes are used for disarming traps. To use them, equip them like you would a weapon, point at the door or container, and "attack".
Your chance of success depends on your Security skill, the difficulty of the lock (in the case of lockpicking - traps have no difficulty) and the quality of the tools you're using. The formula for "% chance to unlock" is:
1.25(Security skill + Luck/10 + Agility/5)(Lockpick multiplier) - Lock Level
Lockpicks and Probes have a limited number of uses before they break, so you should carry a decent supply of them if your skill is low. There are five standard grades of both tools. Only four of these can be found in the game, and only three are available at vendors. Additionally, there is The Skeleton Key, a high quality Lockpick which is acquired by completing the quest line for the Thieves Guild. There are also very low-grade Bent Probes, which can be found in various locations, but are not sold anywhere.