Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: EXASolution 5.0, EXASOL 6.0.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
    • Solution:
      Hide

      Background

      This solution describes how to determine all granted system and object privileges per user. A license for "Graph Search" is required to run the attached query.

      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

      Show
      Background This solution describes how to determine all granted system and object privileges per user. A license for "Graph Search" is required to run the attached query. 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
    • Category 1:
      Database Administration

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: