This is a question that comes up frequently and, as you suggest, once a person has picked up the iPad there's no real protection.
So the common concensus is that you need to set the Autolock to a very short time - or get into the habit of always putting the iPad to Sleep when you leave it unattended. Since it has instant-on, instant-off, there's no real penalty here in terms of getting back to using it again.
In terms of a third-party app providing password protection against an unauthorised user gaining access to any of the iPad's native apps or data, there is no way that this could be done on a non-jail break iPad because the iPad's operating system, iOS, imposes very strict limitations on how apps can interact with each other and this is simply not possible. An individual app could require passwords to access its own data, but not another app's.
So the Autolock screen is the only sure way of providing security.
Tim