Depending on the authentication integration scenario for your Alfresco system, it might not actually store passwords for (most) users in the database. Either way, if passwords are stored they are stored in either MD4 or bcrypt hash form. You should never need to interact with this data directly (even when creating a custom login form), as this is what the Alfresco authentication services already provide.
But to answer the question anyway: users are maintained as nodes within the Alfresco database, meaning that their identity and (hashed) passwords are stored via the alf_node / alf_node_properties tables.
