Oracle 19.13
I posted recently on the first part of this
Persist permissions on an external directory after drop of grantor (0 Bytes)
Am trying to reverse out grants from DBA accounts that granted permissions on external directories. Now the initial part of this is known behaviour (not documented anywhere that I can find), that a dropped DBA will revoke the grant. This is a follow on from that and the recreation steps that Im hoping someone will know a workaround on how to do that as sys.
SQL> show user
USER is "SYS"
SQL>
SQL> -- create 2 dba users
SQL>
SQL> create user dba_user1 identified by 123;
User created.
SQL> create user dba_user2 identified by 123;
User created.
SQL> grant dba to dba_user1;
Grant succeeded.
SQL> grant dba to dba_user2;
Grant succeeded.
SQL> -- create user for grant
SQL> create user tuser1;
User created.
SQL> -- connect as dba_user1 and create directory and grant read to tuser1
SQL> conn dba_user1/123
Connected.
SQL>
SQL> create or replace directory t_dir2 as '/tmp';
Directory created.
-- grant on the dir to user
SQL> grant read on directory t_dir2 to tuser1;
Grant succeeded.
SQL> set pages 999 lines 300
SQL> col grantor format a20
SQL> col grantee format a20
SQL> col table_name format a10
SQL> select grantor, grantee, table_name
2 from dba_tab_privs
3 where table_name like 'T_DIR2'
4 and grantee like 'TUSER1';
SQL> /
GRANTOR GRANTEE TABLE_NAME
-------------------- -------------------- ----------
DBA_USER1 TUSER1 T_DIR2
SQL>
-- so we see the grantor is the DBA account that created the directory
-- now login as second DBA, run the same grant and check the privs again.
SQL> conn dba_user2/123
Connected.
SQL>
SQL> grant read on directory t_dir2 to tuser1;
Grant succeeded.
SQL>
SQL> select grantor, grantee, table_name
2 from dba_tab_privs
3 where table_name like 'T_DIR2'
4 and grantee like 'TUSER1';
GRANTOR GRANTEE TABLE_NAME
-------------------- -------------------- ----------
DBA_USER1 TUSER1 T_DIR2
SYS TUSER1 T_DIR2
SQL>
-- and now SYS is the grantor for the second DBA account, lets remove that grant and try remove the DBA_USER1 grant as well
SQL> revoke read on directory t_dir2 from tuser1;
Revoke succeeded.
SQL> select grantor, grantee, table_name
2 from dba_tab_privs
3 where table_name like 'T_DIR2'
4 and grantee like 'TUSER1';
GRANTOR GRANTEE TABLE_NAME
-------------------- -------------------- ----------
DBA_USER1 TUSER1 T_DIR2
SQL>
-- its revoked read from SYS but not the DBA_USER1... lets try that as sys
SQL>
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> revoke read on directory t_dir2 from tuser1;
revoke read on directory t_dir2 from tuser1
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant
-- grant is definitely there
SQL>
SQL> select grantor, grantee, table_name
2 from dba_tab_privs
3 where table_name like 'T_DIR2'
4 and grantee like 'TUSER1';
GRANTOR GRANTEE TABLE_NAME
-------------------- -------------------- ----------
DBA_USER1 TUSER1 T_DIR2
SQL>
So SYS seems to be missing something here. Without logging in as the user who created the directory and gave the intial grant, I cant see how to remove it. Any ideas?