Got hit with this recently.
DBA grants read on external directory to a user.
Some time later the DBA account is dropped.
Users permissions on the external directory is dropped.
create user test_dba identified by 123;
grant connect, dba to test_dba;
create user test_user1;
create user test_user2;
create table test_user1.t1 (col1 number);
-- connect as DBA
SQL> show user
USER is "TEST_DBA"
SQL> grant read on test_user1.t1 to test_user2;
Grant succeeded.
SQL> create or replace directory test_dir as '/tmp';
Directory created.
SQL> grant read on directory test_dir to test_user2;
Grant succeeded.
SQL> col grantee format a15
SQL> col owner format a15
SQL> col grantor format a15
SQL> col table_name format a15
SQL> col type format a15
SQL> select grantee, owner, table_name, grantor, type
2 from dba_tab_privs
3 where grantee = 'TEST_USER2'
4 ;
GRANTEE OWNER TABLE_NAME GRANTOR TYPE
--------------- --------------- --------------- --------------- ---------------
TEST_USER2 SYS TEST_DIR TEST_DBA DIRECTORY
TEST_USER2 TEST_USER1 T1 TEST_USER1 TABLE
SQL>
So the grantor on the READ from the table T1 is recorded as the table owner TEST_USER1
but the grant on the directory TEST_DIR is recorded as TEST_DBA.
If I drop TEST_DBA that permission is then lost on the directory but the grant remains on the table.
Is there a way to persist the grant on the directoy so its not affected by the drop of the granting user?
A simple workaround answer is use SYS (or a generic account that would never be dropped) or a procedure that handles that, but I would have thought this is a bit weak having to do that, would be handy if was an extra command when running the grant that would make this just happen.