Even as a Bond appreciater, I've gotta agree with you that the Bond title songs, while hella entertaining, usually aren't great. (Alicia, a Bond fan to the point of reading lots of Fleming and Connery's-the-only-Bond opinionating, introduced me to Goldfinger, and made a point of fast-forwarding through the opening titles. I think she called the song boring.) They ARE great for blasting other ear worms out of your head, though! I had a Britney Spears song stuck in my head once, but I got rid of it with Tom Jones' "Thunderball." I have the 30th-anniversary 2-CD set of each song up through "License to Kill," plus the Anthony Newley demo of "Goldfinger," the Shirley Bassey and Dionne Warwick versions of "Mr. Kiss Kiss Bang Bang," and the first, unused "You Only Live Twice" that was about gambling. Fun stuff.
Maybe the only great John Barry Bond song isn't even for the opening titles, and that's "We Have All the Time in the World" from On Her Majesty's Secret Service. Wasn't that the last song Louis Armstrong ever recorded?
The corporate atmosphere in which artists must write the Bond songs doesn't encourage greatness. I'd heard once that when John Barry was working with Tim Rice on the song for Octopussy, supposedly he told Rice to write the lyrics for nine songs. This was because the film had eight producers, and Barry figured each one would try to justify his or her paycheck by vetoing a song idea. Apparently that's exactly what happened.
* I'm kidding. But I say this: Please let there NEVER be a Bond song by Oasis. I had that idea recently and thought it horribly wrong.