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 lockpicks, and six grades of probes. However, none of the secret master grade equipment made it into the final game aside from Sirollus Saccus' repair hammers, and only three of the grades 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.