How to find out all the users who have privileges on a specific schema?

[Former Member]

I have a couple of users, and I don't want to check users' privileges one by one.

Thanks.

[Prabhith · Accepted solution]

Hi Former Member,

Please check if the following blog from Tomas would help you out.

http://scn.sap.com/community/hana-in-memory/blog/2012/08/31/sap-hana--how-to-analyze-who-has-access-...

BR

Prabhith

[Former Member · Accepted solution]

Hi, Probhith,

Tomas's blog has much more information. I found what I need.

This simple SQL can solve my problem.

SELECT GRANTEE, GRANTEE_TYPE, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, PRIVILEGE FROM SYS.GRANTED_PRIVILEGES WHERE OBJECT_TYPE = 'SCHEMA' AND SCHEMA_NAME = <myschema>

Thank you very much.

Leslie

[Prabhith · Accepted solution]

HI Former Member,

Great to hear that your problem is solved.

Request you to close the thread(if possible), so that people can use your solution for their reference as-well.

BR

Prabhith

[Former Member]

Hi, Ravi,

Thanks for answering.

I used SYSTEM to try the statement

SELECT USER_NAME FROM EFFECTIVE_PRIVILEGES WHERE SCHEMA_NAME = <MYSCHEMA>;

I got an error saying

SAP DBTech JDBC: [8]: invalid argument: SELECT without WHERE clause USER_NAME = ... not supported.

I should be able to find out which user has privileges on the schema, but I have to test each user in my system.

Regards,

Leslie

[former_member184768]

Hi Leslie,

Get the username for a given schema name from SAP HANA Reference: EFFECTIVE_PRIVILEGES.

Regards,

Ravi