Team Exasol
Team Exasol

Background

This solution describes how to determine all granted system and object privileges per user. 

Explanation

The following SQL statement will return:

  • PUBLIC privileges
  • direct privileges
  • object definitions
  • system privileges
with all_granted_roles as (
	with k1 as (
		select
			grantee, granted_role,
			( select max(user_name) from exa_dba_users where user_name = grantee ) as user_name
		from EXA_DBA_ROLE_PRIVS rp
	
	union all
	
		select
			user_name, 'PUBLIC', user_name
		from exa_dba_users
	)
	select CONNECT_BY_ROOT(GRANTEE) grantee, GRANTED_ROLE
	from k1
	CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
	start with user_name is not null
)

, all_object_privileges as (
	SELECT gr.GRANTEE as USER_NAME, gr.GRANTED_ROLE, op.PRIVILEGE, op.object_schema, op.object_name
	from all_granted_roles gr
	join SYS.EXA_DBA_OBJ_PRIVS op
	  on gr.GRANTED_ROLE = op.GRANTEE

	union all

	SELECT us.USER_NAME, null, op.PRIVILEGE, op.object_schema, op.object_name
	from SYS.EXA_DBA_USERS us
	join SYS.EXA_DBA_OBJ_PRIVS op
	  on us.USER_NAME = op.GRANTEE
)

, all_system_privileges as (
	SELECT gr.GRANTEE as user_name, gr.granted_role, op.PRIVILEGE
	from all_granted_roles gr
	join SYS.EXA_DBA_SYS_PRIVS op
	  on gr.GRANTED_ROLE = op.GRANTEE

	union all

	SELECT us.USER_NAME, null, op.PRIVILEGE
	from SYS.EXA_DBA_USERS us
	join SYS.EXA_DBA_SYS_PRIVS op
	  on us.USER_NAME = op.GRANTEE
)

select user_name, granted_role, privilege, object_schema, object_name
	from all_object_privileges
union all
select user_name, granted_role, privilege, null, null
	from all_system_privileges
;

If you get an error message, such as "CONNECT BY loop in user data", you may need to edit the SQL slightly:

with all_granted_roles as (
	with k1 as (
		select
			grantee, granted_role,
			( select max(user_name) from exa_dba_users where user_name = grantee ) as user_name
		from EXA_DBA_ROLE_PRIVS rp
	
	union all
	
		select
			user_name, 'PUBLIC', user_name
		from exa_dba_users
	)
	select CONNECT_BY_ROOT(GRANTEE) grantee, GRANTED_ROLE
	from k1
	CONNECT BY NOCYCLE GRANTEE = PRIOR GRANTED_ROLE
	start with user_name is not null
)
....

Additional References

https://docs.exasol.com/database_concepts/privileges.htm

https://docs.exasol.com/sql/grant.htm