WITH RECURSIVE t AS
(
  SELECT u1.user, u1.host, d1.db, u1.select_priv, IF(u1.password <> '', u1.password, u1.authentication_string) AS password, u1.is_role, u1.default_role
    FROM user AS u1
    LEFT JOIN db AS d1
      ON (u1.user = d1.user AND u1.host = d1.host)
   WHERE u1.plugin IN ('', 'mysql_native_password')
UNION
  SELECT u.user, u.host, t.db, u.select_priv, IF(u.password <> '', u.password, u.authentication_string), u.is_role, u.default_role
    FROM user AS u
    LEFT JOIN tables_priv AS t
      ON (u.user = t.user AND u.host = t.host)
   WHERE u.plugin IN ('', 'mysql_native_password')
)
, users AS
(
  SELECT t.user, t.host, t.db, t.select_priv, t.password, t.default_role AS role
    FROM t
   WHERE t.is_role <> 'Y'
UNION
  SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
    FROM t
    JOIN users AS u ON (t.user = u.role)
    LEFT JOIN roles_mapping AS r ON (t.user = r.user)
)
SELECT DISTINCT t.user, t.host, t.db, t.select_priv, t.password
  FROM users AS t
 WHERE t.user <> 'root'
;
