Apache sentry - Get sentry groups to which a given database/tables has been assigned to

Question

I want get for a given database/table the list of groups this database/table has been granted access in sentry.

Answer 1

There does not appear to be a Sentry SHOW command for this purpose in the documentation.

This blog post suggests that you can instead query the Sentry database directly (assuming you are using the Sentry service, not policy files).

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.

You will need the Sentry database password - the blog post gives a suggestion for retrieving it if you do not know it.

An example query for a PostgreSQL database is given:

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";

However, I have not tried this query myself.

Answer 2

This should work for MySQL:

SELECT R.ROLE_NAME, G.GROUP_NAME
FROM SENTRY_ROLE_GROUP_MAP RGM
JOIN SENTRY_ROLE R ON R.ROLE_ID=RGM.ROLE_ID
JOIN SENTRY_GROUP G ON G.GROUP_ID=RGM.GROUP_ID;