"Illegal drugs" have been found in staff lockers at Wales's largest hospital with "no consequences", a review has found.