EXA_DBA_OBJ_PRIVS shows duplicate entries for grants by deleted users

Detail name	Value
Changelog Number	13046
Type	Bug
Status	Open
Affected Versions	Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0

Description

EXA_DBA_OBJ_PRIVS shows privileges as granted by SYS if the user granting them was dropped. This can lead to a duplication of exactly the same entry

• if several users that granted the same privilege on the same object were dropped
• or a single user granted the same privilege also granted by SYS and was then dropped

Preparation:

Grant the same privileges both by SYS and a user.

-- create the objects
create schema test;
create table t( i int);

-- grant object privileges by SYS
grant usage on test to public;
grant select on t to public;

-- create another user for granting
create user foo identified by bar;
grant create session to foo;
grant dba to foo;


-- grant the same privileges as the other user
impersonate foo;
grant usage on test to public;
grant select on test.t to public;
impersonate sys;

-- confirm the privileges are shown as granted by two different users
-- this is consistent
select * from exa_dba_obj_privs
where (object_type='SCHEMA' and object_name='TEST')
   or (object_schema='TEST' and object_name='T');

-- OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE PRIVILEGE GRANTEE GRANTOR OWNER 
-- ------------- ----------- ----------- --------- ------- ------- ----- 
-- (null)        TEST        SCHEMA      USAGE     PUBLIC  SYS     SYS   
-- (null)        TEST        SCHEMA      USAGE     PUBLIC  FOO     SYS   
-- TEST          T           TABLE       SELECT    PUBLIC  SYS     SYS   
-- TEST          T           TABLE       SELECT    PUBLIC  FOO     SYS  

Example
Drop the user and see SYS duplicate entries because FOO is now shown as SYS.

-- dropping a GRANTOR shows SYS as GRANTOR
drop user foo;

-- now there are multiple identical entries
select * from exa_dba_obj_privs;

-- OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE PRIVILEGE GRANTEE GRANTOR OWNER 
-- ------------- ----------- ----------- --------- ------- ------- ----- 
-- (null)        TEST        SCHEMA      USAGE     PUBLIC  SYS     SYS   
-- (null)        TEST        SCHEMA      USAGE     PUBLIC  SYS     SYS   
-- TEST          T           TABLE       SELECT    PUBLIC  SYS     SYS   
-- TEST          T           TABLE       SELECT    PUBLIC  SYS     SYS   

Workaround

Revoke and re-grant the duplicate privileges.

-- revoke privileges
revoke usage on test from public;
revoke select on t from public;

-- grant them again
grant usage on test to public;
grant select on t to public;

-- now the entries are cleaned up
select * from exa_dba_obj_privs;

-- OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE PRIVILEGE GRANTEE GRANTOR OWNER 
-- ------------- ----------- ----------- --------- ------- ------- ----- 
-- (null)        TEST        SCHEMA      USAGE     PUBLIC  SYS     SYS   
-- TEST          T           TABLE       SELECT    PUBLIC  SYS     SYS   

Fix

The table is consistent without duplicate entries.