Checking Analytic Privileges on Objects in HANA SQL

[Former Member]

Hi All,

I'm trying to determine how to check for Analytic Privileges on a calculation view in HANA SQL.  For example, does a particular user have the Analytic Privileges required to perform a select on a certain calculation view.

Thanks,

James

[Former Member · Accepted solution]

Hi James,

You can use the GRANTED_PRIVILEGES system view. The following SQL should give you what you need if you substitute <CALC VIEW> for the calculation view you want to check privileges on.

SELECT GRANTEE

FROM GRANTED_PRIVILEGES

WHERE OBJECT_TYPE = 'ANALYTICALPRIVILEGE'

AND OBJECT_NAME = '<CALC VIEW>'

Regards,

Gary

[Former Member · Accepted solution]

Gary,

Is there a way to see which Calculation views, Analytic Views, etc., are included under the Analytic Privileges listed in GRANTED_PRIVILEGES?  I can see that Users and Roles are linked in GRANTED_ROLES, which gets me to the user (more often than not we have our Analytic Privileges granted to a role, not the users directly). 

Thanks,

James

[Former Member · Accepted solution]

Hi James,

There's several ways to do this, and plenty of system views have been created by HANA to help you out. The following SQL will give you the analytic privileges and associated users. If the analytic privilege has been assigned to a role, then a join is created to the granted_privileges system view to bring back all users assigned to the role. There's probably others ways to do this, but this should get you on your way. Other options may be to look at the ACCESSIBLE_VIEWS system table which offers something a little similiar. If you take a look at the following url, it has the list of system views and the associated information you can get from each.

SELECT

  CASE WHEN A.GRANTEE_TYPE = 'USER'

THEN A.GRANTEE WHEN A.GRANTEE_TYPE = 'ROLE'

THEN B.GRANTEE

END as USER,

  A.GRANTEE AS ROLE_USER_NAME,

  A.GRANTEE_TYPE AS INHERITED_AS_ROLE_OR_USER,

  OBJECT_NAME

FROM GRANTED_PRIVILEGES A

LEFT OUTER JOIN GRANTED_ROLES B ON A.GRANTEE = B.ROLE_NAME

WHERE OBJECT_TYPE = 'ANALYTICALPRIVILEGE'

[Former Member · Accepted solution]

Gary,

Thanks for all of the help.  The Accessible Views view has just what I'm looking for. 

Thanks,

James