question Re: How to find out the roles / groups assigned to a schema / DB in Hive in Support Questions

How to find out the roles / groups assigned to a schema / DB in Hive

prakodi — Fri, 10 Feb 2023 14:17:47 GMT

Hi All,

I want to find out what are the roles / groups assigned to a DB / Schema in Hive, how I can get those details ? please suggest.

Thank you.

Regards,

Re: How to find out the roles / groups assigned to a schema / DB in Hive

JoseManuel — Mon, 13 Feb 2023 18:08:33 GMT

What is the specific version of HDP /CDH / CDP ?

Re: How to find out the roles / groups assigned to a schema / DB in Hive

prakodi — Tue, 14 Feb 2023 12:43:05 GMT

CDH 6.3

Re: How to find out the roles / groups assigned to a schema / DB in Hive

JoseManuel — Tue, 14 Feb 2023 16:41:53 GMT

Use the following command reference

These are to be executed from Hive Beeline to view Role Privileges and Assignments

> SHOW ROLES;
List available roles

> SHOW CURRENT ROLES;
List roles assigned to current user/group

> SHOW ROLE GRANT GROUP <GROUP NAME>;
Lists the roles that are assigned to the specified group.

> SHOW GRANT ROLE <ROLE>;
Lists the permissions that have been granted to the specified role.

>SHOW GRANT ROLE <role name> ON <object type> <object name>
Lists the permissions that a role has on an object.

Re: How to find out the roles / groups assigned to a schema / DB in Hive

DianaTorres — Fri, 17 Feb 2023 17:45:55 GMT

@prakodi Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. Thanks

Re: How to find out the roles / groups assigned to a schema / DB in Hive

prakodi — Fri, 17 Feb 2023 17:55:34 GMT

Hi,

Thank you for the reply, but if I know the role or group, I can easily get the details from above commands, but for a specific DB , if I want to find out what are the roles assigned, is there a way to do it ?

Assume, my schema name is schema1, how to find out the roles assigned to this schema ?

Thank you.

Regards,

Re: How to find out the roles / groups assigned to a schema / DB in Hive

JoseManuel — Fri, 17 Feb 2023 18:46:01 GMT

PK,

If you have access to the Sentry Store backend use a query like this:

SELECT
r.ROLE_NAME,
g.GROUP_NAME,
p.*,
FROM_UNIXTIME(p.CREATE_TIME / 1000) as CREATE_TIME_DATE
FROM
SENTRY_ROLE r
INNER JOIN SENTRY_ROLE_GROUP_MAP rgm ON r.ROLE_ID = rgm.ROLE_ID
INNER JOIN SENTRY_GROUP g ON g.GROUP_ID = rgm.GROUP_ID
INNER JOIN SENTRY_ROLE_DB_PRIVILEGE_MAP rpm ON r.ROLE_ID = rpm.ROLE_ID
INNER JOIN SENTRY_DB_PRIVILEGE p ON p.DB_PRIVILEGE_ID = rpm.DB_PRIVILEGE_ID;

Hope it helps,

-JMP

Re: How to find out the roles / groups assigned to a schema / DB in Hive

prakodi — Fri, 17 Feb 2023 19:04:25 GMT

Thank you very much.

Regards,

Re: How to find out the roles / groups assigned to a schema / DB in Hive

CelsoC — Tue, 21 Jan 2025 20:46:07 GMT

Hello @JoseManuel ,

Is there any possible query to have a similar result with Ranger (CDP 7.1.7 sp2)?

Regards,

Celso

Re: How to find out the roles / groups assigned to a schema / DB in Hive

DianaTorres — Wed, 22 Jan 2025 00:36:27 GMT

@CelsoC Welcome to the Cloudera Community!
As this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post. Thanks.

Re: How to find out the roles / groups assigned to a schema / DB in Hive

cdebernardi — Wed, 22 Jan 2025 20:20:32 GMT

I tested this in 7.1.9 SP1 CHF 4, with Postgres