About a month ago there was a question in Firebird (CZ) group how to find whether the user is able to use particular role or to get all roles for user.
As you probably know, a lot of stuff (almost everything) is in system catalog (system tables). The only problem is to figure out what’s the right set of parameters to use. To get all roles with users able to use it, you can use:
select rdb$relation_name as "Role", rdb$user as "User" from rdb$user_privileges where rdb$privilege = 'M' and rdb$user_type = 8 and rdb$object_type = 13;
rdb$privilege = 'M' is to get all member of privileges, the
rdb$user_type = 8 is about getting records for users and finally
rdb$object_type = 13 filters only for roles records.
rdb$user_privileges table contains all privileges defined for the database – tables, stored procedures, triggers, roles, …, but also take into account that current versions of Firebird (<3.0) are storing users (only, not roles) in system wide security database.