DROP ROLE does not delete entries in EXA_DBA_RESTRICTED_OBJ_PRIVS

Detail name	Value
Changelog Number	21909
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 8.29.8, Exasol 8.34.0
Resolution Date	2025-03-10

Description

Dropping a ROLE with DROP ROLE does not remove permissions in EXA_DBA_RESTRICTED_OBJ_PRIVS (i.e., permissions to connections). Note that the remaining permission has no security impact as the corresponding object no longer exists and it has no effect as a consequence.

Preparation:

CREATE SCHEMA test;
CREATE ROLE CONNECTION_TEST_ROLE;
CREATE OR REPLACE CONNECTION TESTCONNECTION TO 'A' USER 'B' IDENTIFIED BY 'C';
GRANT ACCESS ON CONNECTION TESTCONNECTION FOR TEST TO CONNECTION_TEST_ROLE;

Example:

DROP ROLE CONNECTION_TEST_ROLE;
-- This should be empty, but the result contains 1 row.
SELECT * FROM exa_dba_restricted_obj_privs WHERE grantee = 'CONNECTION_TEST_ROLE';

Workaround

• Revoke the permissions before dropping the role:

REVOKE ACCESS ON CONNECTION TESTCONNECTION FOR TEST FROM CONNECTION_TEST_ROLE;
DROP ROLE CONNECTION_TEST_ROLE;
-- Result: 0 rows.
SELECT * FROM exa_dba_restricted_obj_privs WHERE grantee = 'CONNECTION_TEST_ROLE';

• Recreate the connection:

DROP ROLE CONNECTION_TEST_ROLE;
-- This should be empty, but the result contains 1 row.
SELECT * FROM exa_dba_restricted_obj_privs WHERE grantee = 'CONNECTION_TEST_ROLE';
CREATE OR REPLACE CONNECTION TESTCONNECTION TO 'A' USER 'B' IDENTIFIED BY 'C';
-- Result: 0 rows.
SELECT * FROM exa_dba_restricted_obj_privs WHERE grantee = 'CONNECTION_TEST_ROLE';

• Recreate the schema:

DROP ROLE CONNECTION_TEST_ROLE;
-- This should be empty, but the result contains 1 row.
SELECT * FROM exa_dba_restricted_obj_privs WHERE grantee = 'CONNECTION_TEST_ROLE';
DROP SCHEMA TEST CASCADE;
CREATE SCHEMA TEST;
-- Result: 0 rows.
SELECT * FROM exa_dba_restricted_obj_privs WHERE grantee = 'CONNECTION_TEST_ROLE';

Fix

Dropping the role removes the entries from exa_dba_restricted_obj_privs.