This is supported by the built-in Screen Sharing as of OS X Lion, if you have set up multiple accounts:
Per-user screen sharing
You can remotely log in to a Mac with any user account on that computer and control it, without interrupting someone else who might be using the computer under a different login.
This works fine from Mac to Mac. To control from Windows, apparently one needs to "kickstart" ARD once. This can be done using the command line, by following Apple's Apple Remote Desktop: Configuring remotely via command line (kickstart). Or: in System Preferences disable Screen Sharing, Remote Login, Remote Management and Remote Apple Events, then enable all again, and finally disable all and just enable Screen Sharing.
But even when doing the above, my tests with an old XP machine and the latest version of UltraVNC were not very successful:
When the Mac was at the login window, using UltraVNC would really take over control of that Mac's screen, just like in the old days. Hence: both the Mac and the XP box would show exactly the same, and share a single session.
When the Mac user was logged in, starting VNC would show the background of the login window, but most often empty. Moving the mouse in Windows would make the Mac cursor change into a beach ball. Whenever the login window was not empty, it would still not respond to any clicks.
Using Remote Management rather than Screen Sharing made no difference.
As I do not need this myself, I did not test a lot though. A different OS, or another viewer, might yield different results. But the following quote from the most recent RealVNC's release notes are not very hopeful:
Connections to Screen Sharing built-in to Mac OS X 10.7 (Lion) are not supported.
Workaround: None.
Status: Under consideration.
As an aside: beware not to test using a remote session from your Mac to some Windows box, back to the Mac, or using something like Parallels running Windows on the very same Mac. That yields an endless loop with a Droste Effect; reboot required...
For older versions of OS X, it is possible using Vine Server (OSXvnc). I never used it, but the creators claim:
[..] in Tiger (Mac OS 10.4) all those desktops can be accessed simultaneously using Vine Server (OSXvnc). This allows multiple users to be logged in, each using his or her own desktop on the same Mac.
[..]
All they need is a computer or PDA running a VNC viewer to access Vine Server (OSXvnc). Now you have a use for all those old PC's!
However:
Each user MUST be logged in using Fast User Switching. If you reboot the computer you will need to go and log-in each user with Fast User Switching to enable access via VNC.
And using the very same software simultaneously might yield problems (like when temporary files are not stored in a user area), or might break the license agreements.
I don't know if this still works in Leopard and Snow Leopard. (But a recent post on Server Fault might indicate it's tested on a recent version of OS X, hence Snow Leopard?)