It does require activation immediately after installation, an internet connection is part of the system requirements. There was a big brouhaha over the limited number of re-installs you could perform, but those restrictions have been loosened considerably. I believe currently you are allowed five installs each on five separate machines before you hit the SecuROM wall, and as previously mentioned, the developers have promised to remove SecuROM entirely at some future point.
Frankly, this is the best game I have ever played. I'm simply awed by the creativity and attention to detail that went into this game, and haven't enjoyed playing a game like this since the original Half-Life. Opinions will vary, but I think the quality of this game speaks for itself. And unlike a lot of games I've played on day one of release, Bioshock has not crashed on me even once. Pretty amazing these days for a version 1.0 game, if you ask me.