If you had a simple, single element lens, then the way you would focus on something closer than infinity would be to physically move the whole lens further away from the film/sensor plane. The further you move it, the closer you focus the thing. With a simple lens, you could focus very very closely. Basically as close as you can get to the glass when the lens is moved out far enough.
With actual commercial lenses, there are various problems you run into that stop you from focusing this closely:
1) Even if it was a simple lens system, the lens will only be built with some maximum amount of range that you can slide the lens back and forth, since lenses must be mounted in some sort of housing, and cannot just float in an abstract optical theoretical space. If the manufacturer design doesn't allow enough sliding room, then you wouldn't be able to focus on something arbitrarily close.
2) Well why don't manufacturers just allow enough slide to focus on anything? Because most complex lenses with multiple elements (which are necessary to fit on commercial lens moutns and correct for various optical flaws) do NOT focus by merely moving the entire lens forward and backward, but instead focus by moving just one "focusing group" back and forth. Sometimes, this focusing group is sandwiched in between other elements in the lens and cannot physically move far enough to focus on very close things without crashing into other glass elements.
3) Also, lens manufacturers want to avoid vignetting, and the further you slide a lens group forward, the more light is going to hit the walls inside the lens and be lost, unless you have sufficiently large internal lenses to catch it. If you just slide the focusing group forward without large enough lenses further back, then you'd get vignetting. In order to keep costs down and not have us lugging around lenses with 8 lb. pieces of glass in them, the manufacturers impose a minimum focusing distance and only let the focusing group slide that far, in order to help prevent vignetting and guaranteeing quality.
What an extension tube does is to let you get around this by physically moving the ENTIRE lens forward, instead of just the focusing group. This will always allow closer focusing, no matter what the lens design is. All in all, the effects are:
1) You will gain magnification ROUGHLY according to the following equations (these are not exact for a variety of reasons, but are pretty close):
a) Look up the maximum magnification factor for your lens online (you can calculate it yourself too using minimim focus distance and FL, but it's more complicated and involves some assumptions about lens design. Better to use known or empirical values). Let's say you use a 50mm lens and it has 0.1x max magnification. multiply this number by the focal length: 50mm * 0.1 = 5mm.
b) Now add the length of the extension tubes you put on the body to the above result. If we use 10mm of extension tubes, 10 + 5 = 15mm.
c) This new number divided by focal length = new magnification: 15 / 50 = 0.3x magnification.
edit: as mentioned in tirediron's link, this can be simplified I guess down to (mm extension / FL) for the gain only (not the total resulting magnification, must add original for that).
Note that with longer focal lengths, more extension tube will have to be used for the same magnification effect. If a 300mm lens has 0.1x magnification, and we add 10mm of extension tubes, its new magnification would only be 0.13x, a much more modest increase than on the 50mm lens.
Also note that if you add sufficiently many extension tubes, it is possible to end up with a close focus distance that is actually behind/inside your lens + its extension tubes, which means you can't focus on anything.
2) You will lose light directly proportionally to the amount of magnification you gain, at LEAST. So if your magnification at closest focus increases by a factor of 3 as in the above example, you will also get 1/3 as much light. This is due tot he fact that the lens is only letting is as much light as before, but now only 1/3 as much of it is falling on your sensor (because you magnified the image circle and part of it now falls off to the side of your sensor). In reality, you lose even more light than this, because some light will also start crashing into the sides of your extension tubes / camera body. This extra darkening will show up as vignetting.
3) You will lose the ability to focus at infinity. This is because the internal focusing system of your lens again has physical limits placed on it, and the focusing elements cannot move far enough back to compensate for the amount you moved the lens forward with your extension tubes (they're only built to go JUST far enough back for infinity when normally mounted). The further forward you move it, the further your lens will fail to meet infinity focus by. Usually this doesn't matter, because if you're putting on macro tubes, you don't WANT to focus at infinity anyway.
4) Your lens will gain additional chromatic and spherical aberration and astigmatism and coma, etc. (color fringing, softness, blur that increases as you move away from image center, etc.). Lenses are designed to have minimal aberrations when used attached to the lens mount, and by moving it further away, you are moving away from the optimally lowest point of optical flaws, so your image will have lower quality than normal. This may be more or less noticeable, depending on the lens.