cancel
Showing results for 
Search instead for 
Did you mean: 

Regarding privileges assigned to users in SAP Hana database

1,382

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.

View Entire Topic
pfefferf
Active Contributor

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).