We can see existing user accounts, along with their superuser status, by accessing the users table in the system_auth keyspace:
SELECT * FROM "system_auth"."users";
Note that the use of the period between the keyspace name and the table name simply instructs CQL that we'd like to look into the system_auth keyspace, regardless of which keyspace, if any, has been activated by an USE statement.
As we can see, user accounts are stored quite transparently in the system_auth.users table:
You may be wondering where and how passwords are stored. These live in a separate table, system_auth.credentials; passwords are not stored ...