Regarding privileges assigned to users in SAP Hana database

[former_member409309]

Dear Guru's,

Is there any standard view to find out of privileges assigned to mass users?

Currently we have limitation to display privileges assigned t only one user at a time using "effective_privileges" system view. Please help.

Also how to find out a particular privilege assigned in which roles?

thanks in advance.

[pfefferf · Accepted solution]

System view EFFECTIVE_PRIVILEGES is the right source. To avoid the limitation to one user, you can a little anonymous block (like following) which gets for all users the effective privileges:

do
begin
  declare v_i integer;
  declare v_user nvarchar(256);
  declare t_ep_all table like effective_privileges;
  
  t_users = select user_name from users;
  
  for v_i in 1..record_count(:t_users) do
    v_user = :t_users.user_name[v_i];
	t_ep = select * from effective_privileges where user_name = :v_user;
	:t_ep_all.insert(:t_ep);
  end for;
  
  select * from :t_ep_all;
end;

Regarding the question how to find a to what roles a specific privilege is assigned you can use system view EFFECTIVE_PRIVILEGE_GRANTEES (GRANTEE_TYPE = 'ROLE' defines that the privilege is assigned to the role mentioned in column GRANTEE).