RDBMS ORACLE - Quale grant dare per poter effettuare una truncate table

Qual è la corretta grant per poter troncare una tabelle di un altro utente in oracle?

--------------------------------------------------------------------

• Connettersi come sys as sysdba

select user from dual;

USER                          

------------------------------

SYS

• Eseguire il seguente script per dare le grant.

declare

      onlineSchema varchar2(500) := 'DWH_ANALYTICS';

      archiveSchema varchar2(500) := 'DWH_MD';

begin

      execute immediate 'grant  drop any table to '||onlineSchema;

end;

PL/SQL procedure successfully completed.

-----------------

select * from all_tab_privs where table_name='TEST_TRUNCATE_TABLE' and GRANTOR='DWH_MD';

no rows selected

-----------------

• Connettersi come DWH_ANALYTICS

select user from dual;

USER                          

------------------------------

DWH_ANALYTICS

SELECT * FROM DWH_MD.TEST_TRUNCATE_TABLE;

ORA-01031: insufficient privileges

01031. 00000 -  "insufficient privileges"

*Cause:    An attempt was made to perform a database operation without

           the necessary privileges.

*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

Error at Line: 1 Column: 23

• Eseguire la TRUNCATE della tabella

TRUNCATE TABLE DWH_MD.TEST_TRUNCATE_TABLE;

Table DWH_MD.TEST_TRUNCATE_TABLE truncated.

-------------------------------------------------------------------

Connettersi nuovamente come sys e revocare il privilegio

select user from dual;

USER                          

------------------------------

SYS

• Eseguire lo script

declare

      onlineSchema varchar2(500) := 'DWH_ANALYTICS';

      archiveSchema varchar2(500) := 'DWH_MD';

begin

      execute immediate 'revoke drop any table from '||onlineSchema;

end;

PL/SQL procedure successfully completed.

-------

select * from all_tab_privs where table_name='TEST_TRUNCATE_TABLE' and GRANTOR='DWH_MD';

no rows selected

-------

Documentazione Oracle:

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TRUNCATE-TABLE.html