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
