How to determine Sentry Group to Role Mapping with CDH

Apache Sentry is system within the Hadoop ecosystem for enforcing fine grained role based access control to data and metadata stored within a Hadoop cluster.

With Sentry users are assigned to groups (for example in LDAP / Active Directory), the groups are then associated with roles, and the roles are granted permissions. Authorisation information can either be stored in a file on disk or in a relational database such as MySQL or PostgreSQL. In the latter case HiveQL statements are used to list and grant permissions to roles (the commands below are taken from the Cloudera documentation).

To list all the roles in the system (only for sentry admin users):

SHOW ROLES;

To list all the roles in effect for the current user session:

SHOW CURRENT ROLES;

To list all the roles assigned to the given <groupName> (only allowed for Sentry admin users and others users that are part of the group specified by <groupName>):

SHOW ROLE GRANT GROUP <groupName>;

The SHOW statement can also be used to list the privileges that have been granted to a role or all the grants given to a role for a particular object.

To list all the grants for the given <roleName> (only allowed for Sentry admin users and other users that have been granted the role specified by <roleName>):

SHOW GRANT ROLE <roleName>;

To list all the grants for a role on the given <objectName> (only allowed for Sentry admin users and other users that have been granted the role specified by <roleName>):

SHOW GRANT ROLE <roleName> on OBJECT <objectName>;

Obtaining Sentry Server Database Connection Information

However, at present there is no command to show the group to role mapping. The only way to do this is by connecting to the Sentry database and deriving this information from the tables in the database.

If you're using CDH you can determine which node in the cluster is running the Sentry database using Cloudera Manager, navigating to Clusters >  Sentry, then clicking Sentry Server and then Configuration. Here you will find the type of database being used (e.g. MySQL, PostgreSQL, Oracle), the server the databases is running on, it's port, the database name and user. Hopefully you recall the password that you set when you configured Sentry, if not you can obtain this information from either

• The Sentry site XML file on the server running Sentry (note with CDH 5.5.1 onwards credentials are stored in a JCE credential store and no longer appear as plaintext in configuration files - which is a good thing from a security perspective)
• Inspecting the Sentry configuration using the Cloudera Manager API, by making a call to the URI http://<cm server>:<cm port>/api/<version>/clusters/<cluster name>/services/sentry/config?view=full Where
• <cm server> is the hostname of the server running Cloudera Manager
• <cm port> is the port that Cloudera Manager is listening on, the default is 7180
• <api version> is the version of the API - you can determine this from the URI http://<cm server>:<cm port>/api/version
• <cluster name> is the name of the cluster as it appears in Cloudera Manager

Obtaining the Group-Role Mapping from a MySQL Sentry Database

Logon to the server running the Sentry database and run:

mysql -u <db user> --port <port> -p

Then run the SQL query below to obtain the role-group mapping:

SELECT SENTRY_ROLE.ROLE_NAME,SENTRY_GROUP.GROUP_NAME

FROM SENTRY_ROLE_GROUP_MAP

JOIN SENTRY_ROLE ON SENTRY_ROLE.ROLE_ID=SENTRY_ROLE_GROUP_MAP.ROLE_ID

JOIN SENTRY_GROUP ON SENTRY_GROUP.GROUP_ID=SENTRY_ROLE_GROUP_MAP.GROUP_ID;

Obtaining the Group-Role Mapping from a PostgreSQL Sentry Database

The query to use for PostgreSQL is almost the same except that because the table and column names are in capitals we have to surround them in double quotes.

SELECT "SENTRY_ROLE"."ROLE_NAME","SENTRY_GROUP"."GROUP_NAME"

FROM "SENTRY_ROLE_GROUP_MAP"

JOIN "SENTRY_ROLE" ON "SENTRY_ROLE"."ROLE_ID"="SENTRY_ROLE_GROUP_MAP"."ROLE_ID"

JOIN "SENTRY_GROUP" ON "SENTRY_GROUP"."GROUP_ID"="SENTRY_ROLE_GROUP_MAP"."GROUP_ID";