You entered your password when you logged in; when you authenticated yourself. Unix has never done a second password; you authenticated yourself at the front door. You can certainly set up the file protections to keep other users out.
As for password-protecting a specific object, that's -- as others have stated -- an add-on. Or you use the built-in FileVault stuff, or external hardware.
There are current-generation drives that have password protection mechanisms or similar authentication -- USB keydisks and hard disks. Or you can use a commodity drive and the built-in FileVault. This akin to what you remember with the SyQuest drive.
Or consider products such as the following:
http://www.pgp.com/products/wholediskencryption/tech_specs.html
http://www.usbgear.com/USB-Encryption.html
Or what some of us do. Unplug the disk containing the data, and lock it up somewhere safe. If a desk drawer isn't sufficient, there are also physical disk enclosures with physical locks available on the market.
Realize that various of these encryption products aren't worth as much as the proverbial warm bucket of spit; various of these products either use colossally weak encryption, or have mis-implemented encryption. It would not surprise me to learn that some even have back-doors.