@tbf is right; lubrication, and tribology in general, is complicated. That's why there is that high effort to understand it and to design advanced materials.
There are several phenomena that cause the friction force exist and the ones you have neglected causes that oils are superior to water in most industral applications.
In dry sliding we can identify adhesion (dominant for two super-smooth glassy surfaces), asperity skipping and deformation (dominant for two rough and hard surfaces) and ploughing (dominant for sliding hard rough surface against soft one). Some add chemical bonding as separate cause, others consider it as a part of adhesion and another ones consider it as a condition.
The lubricants are chosen to lower the friction and wear and there is no universal superlubricant ideal for any application. One must consider:
- All materials in sliding contact;
- Range of the applied forces;
- Sliding velocities;
- Environment (air/liquid flow, chemical surrounding, sliding frequency, debris presence, ...)
To the question, the mineral oil is good lubricant in case of sliding two metals because it passivates the surfaces and prevents their contact (adhesion is therefore neglected), if the viscosity is low enough it also decreses the interaction between asperities of both surfaces. On the other hand, water can chemically react with the surfaces and because of its low viscosity cannot prevent asperity interaction. But it says nothing in general.
The most common lubricant on the Earth is water - joints in bodies of all vertebrates are water-lubed.
As Abhinav noted, graphite and all solid lubricants mentioned in comments below his answer are good lubricants and you cannot define viscosity there.
Turbomolecular pumps use magnetic bearings where "lubricant" is vacuum.