Actually none of the above is needed (not at least now).
The simple album level (in plain file management terms, it could be equivalent of a folder). I know which pictures I want stored under what kind of albums.

I don't need any caching either (or else I would store them on the Micro SD anyway).

The reason I was hoping that it would be a stand alone application is the ease of use. Actually after posting the above request I was playing some music with the Mediabox and I realized that a simple additional icon in the "Storage & Network" would be good enough. It already adds additional icons when there are uPnP sources found in the local network. The same way an additional icon can be added once the device is connected to the net (called say, "Web albums"). Click on that, and you will be presented with a list of the sites that you have registered with. Click on that one and you get the list of albums registered under that service and you will be presented with a list of thumb nails. Next thing is same what you do with the local items. Double click to view full screen and so on.

Has anyone tried to view the Ovi Share pictures with N900? After several clicking on various areas and opening new windows for different operations, each picture takes eternity to load and there are obviously some kind transition effects that takes even longer for the next one to start to load

Basically there are no differences between the web viewing the same things and downloading it through some application like Mediabox. It's the UI and ease of use that makes a whole lot of difference. If I just wanted to store the pictures somewhere, I could as well create a small web server on my home machine and share it through that. The point here is to have a common platform that big players like Google and Nokia (ovi) are offering and make the best use of those to make the usage easy for average users.