0

I wrote a Procedure which essentially used to drop table.

It works as expected , but the problem is As this Procedure is in "DW" schema and dropping table is in "SCRATCH".

Because of this, we get insufficient privileges.

Any way we can achieve this and solve the "privilege" issue?

CREATE OR REPLACE PROCEDURE   DW.PROC AUTHID CURRENT_USER IS
V_TABLE_NAME  VARCHAR2(255);
V_DELETE_DT NUMBER(33);
V_LIST SYS_REFCURSOR;
BEGIN
    SELECT TO_NUMBER(VALUE) INTO V_DELETE_DT FROM DW.LIST_OF_TABLE
    OPEN V_LIST FOR
    SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE= 'TABLE' AND 
    OBJECT_NAME LIKE '%DLY_BKP%' AND CREATED <=SYSDATE - V_DELETE_DT;
    
     LOOP 
        FETCH V_LIST
        INTO V_TABLE_NAME;
        EXIT WHEN V_LIST%NOTFOUND;
        EXECUTE IMMEDIATE 'DROP TABLE SCRATCH.'||V_TABLE_NAME ;
     END LOOP;
    CLOSE V_LIST; 
END;
Leverage
  • 63
  • 7
  • 2
    [**Prerequisites**: The table must be in your own schema or you must have the DROP ANY TABLE system privilege.](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DROP-TABLE.html#GUID-39D89EDC-155D-4A24-837E-D45DDA757B45) – astentx Feb 14 '23 at 09:15
  • Probably a duplicate of [Grant truncate privilege to some tables of different user in Oracle](https://stackoverflow.com/q/38329034/1509264) (as the solution is identical just replacing `TRUNCATE` with `DROP`). Just please read the comments and caveats on the answers. – MT0 Feb 14 '23 at 09:30
  • @MT0 i did grant execute PROC_NAME to PUBLIC , still getting privilege issue. – Leverage Feb 14 '23 at 09:51
  • @Leverage If you created a procedure, did you create it belonging to `SCRATCH` (and not belonging to `DW`)? – MT0 Feb 14 '23 at 09:54
  • No the procedure has to be in DW . @MT0 – Leverage Feb 14 '23 at 12:45
  • i added authid current user to Procedure now its working at DB level @MT0 – Leverage Feb 14 '23 at 12:53

1 Answers1

1

To drop a table in another schema, you must do one of the following:

  1. Be granted the "DROP ANY TABLE" system privilege. This is highly elevated and it is likely your DBAs will balk at it, but it'll work.

  2. Create a procedure owned by the table owner that accepts a table name as a parameter and issues the drop DDL on your behalf. Grant execute privs on the procedure to your connect user. This is usually the better choice. You can further enhance it to table-drive a list of tables or table name patterns that are white-listed or black-listed for drop, whatever makes sense.

At it's most simplest:

CREATE OR REPLACE PROCEDURE scratch.drop_table(in_table_name IN varchar2)
AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE "'||in_table_name||'"';
END;
/
GRANT EXECUTE ON scratch.drop_table TO dw;
/

Then DW calls it:

BEGIN
  scratch.drop_table('TABLE_TO_DROP');
END;

If for any reason you cannot create this procedure under the SCRATCH schema (which is an unreasonable restriction), you can create it under a privileged account like SYSTEM and also add the table owner name as an additional parameter. Then you most definitely will want to table-drive a set of white-listed owners to prevent misuse of the procedure.

Paul W
  • 5,507
  • 2
  • 2
  • 13