You can certainly theme the "unlock" sound, so that could say, for example, "Access Granted", if you got a recording of that. I am sure there are plenty of sound files out there with something you could use.
There is a tweak called iUnlock which lets you set a custom sound and animation on unlock. Don't know what it's current compatability is like, but you can look into it.
But you can easily create your own Winterboard theme to have a custom unlock sound. Just download any of the existing ones and have a look at them to see how it's done. Search for "unlock sound" in Cydia. Themes can be found in var/stash/Themes.xxxxx/ on your device once they are installed (where xxxxx is unique to your device).
Unfortuately, I don't think there is a tweak which can hook into the "passocde incorrect" event to give an access denied sound. If there is, I'm not aware of it
Hope that helps a little