How to list users who have privileges to access specific schema in SAP HANA?

We want to know who all have access to particular schema. SAP provides a system view GRANTED_PRIVILEGES which stores this information.

Requirement: We want to list down all users who can access particular schema.
Prerequisite: We need database user with CATALOG READ or DATA ADMIN system privilege.
Steps:
  • Connect to the required system via HANA Studio
  • Open Administration perspective,  You can open it from Window -> Open -> Perspective -> Administration Console
  • Execute following SQL in SQL console:

SELECT GRANTEE, GRANTEE_TYPE, OBJECT_TYPE, SCHEMA_NAME, OBJECT_NAME, PRIVILEGE FROM SYS.GRANTED_PRIVILEGES WHERE OBJECT_TYPE = ‘SCHEMA’ AND SCHEMA_NAME = ‘HECD’

Above SQL will list all users which can access HECD schema in SAP HANA.

Leave a Reply