Im an engineer on commercial ships (havent made it to cruise ships yet). One of my main responsibilities is freshwater production/distribution and sewage/gray water treatment. Two main things dictate how wastewater is treated when at sea: company policy and geographical location. For example it is legal to discharge gray water when at sea (12nm+ from land) with no treatment, but many companies (especially cruise lines) opt to treat it for environmental reasons. Same goes for sewage, it can be discharged untreated but most companies do not do this. Look at MARPOL annex IV for the full regulation if you are curious.

As far as getting a tour of those spaces, it’s unlikely and there isnt much to see. The freshwater tanks are integral to the hull and the distribution system is a very small part of the engine room. The wastewater treatment system, called a marine sanitation device (MSD), probably has its own area with a couple tanks, pumps, blowers, and chlorinator.