Search code examples
sqlhiveprestotrino

How to get all roles granted to a user in Trino/Presto?

basically what is the equivalent of hive query in trino/presto.

show role grant user <username>;
Solution

  • we can get the information from information_schema it is self explanatory but i cannot find document about this system defined schema.

    SELECT * 
FROM information_schema.role_authorization_descriptors 
WHERE grantee = 'a_user';

    a more useful query is check whether a user has access to a table

    SELECT *
FROM information_schema.table_privileges
WHERE table_schema = 'a_schema'
AND   table_name = 'a_table'
AND   privilege_type = 'SELECT'
AND   (grantee = 'a_user' OR grantee IN (SELECT role_name
                                      FROM information_schema.role_authorization_descriptors
                                      WHERE grantee = 'a_user'));