How to write a select query that returns multiple results based on the booleans in one row (for spring security authorities query)?

I have a Java web app using Spring Security 5.

I’m using MySQL as the database.

The database table with users in it looks something like this:

USERS TABLE
ID | USERNAME | PASSWORD | ADMIN_ROLE | USER_ROLE | READONLY_ROLE
-----------------------------------------------------------------
1  | Andy     | abc123   | 1          | 1         | 0
2  | Bob      | xyz789   | 0          | 1         | 1
3  | Chuck    | qrs567   | 1          | 0         | 1

Spring Security requires me to give it two queries … one to get the users, and one to get the user’s roles.

Typically the queries look like this:

users-by-username-query="SELECT USERNAME, PASSWORD FROM USERS WHERE USERNAME=?;"

authorities-by-username-query="SELECT USERNAME, ROLENAME FROM TBL_USER_ROLE WHERE USERNAME=?;"

However, my users and roles are in one table.

So, for my “authorities” query, is there a way to write an SQL query on my USERS table (above) that will give me a record for every “role” value that is 1 (true)? A result something like this:

USERNAME | ROLE_NAME
---------------
Andy     | ADMIN_ROLE
Andy     | USER_ROLE
Bob      | USER_ROLE
Bob      | READONLY_ROLE
Chuck    | ADMIN_ROLE
Chuck    | READONLY_ROLE

Answer

Select main.ID,
       main.USERNAME, 
CASE WHEN main.ADMIN_ROLE = 1 
     THEN 'ADMIN_ROLE'
     ELSE NULL END AS ROLE
FROM users main
WHERE ADMIN_ROLE = 1
UNION
SELECT u.ID,
       u.USERNAME,
      CASE WHEN u.user_role = 1 
        THEN 'USER_ROLE'
        ELSE NULL END AS ROLE
FROM USERS u
WHERE USER_ROLE = 1
UNION
SELECT r.ID,
       r.USERNAME,
     CASE WHEN r.READONLY_ROLE = 1 
          THEN 'READONLY_ROLE'
          ELSE NULL END AS ROLE
FROM users r
WHERE READONLY_ROLE = 1


ID  USERNAME    ROLE
 1  Andy      ADMIN_ROLE
 1  Andy      USER_ROLE
 2  Bob       READONLY_ROLE
 2  Bob       USER_ROLE
 3  Chuck     ADMIN_ROLE
 3  Chuck     READONLY_ROLE