-1

I have created below Oracle Procedure where i am granting DML priviledges on all tables to TST user. Now i also want to grant Truncate priviledge to TST user in the same Procedure but dont know how to do it.

CREATE OR REPLACE PROCEDURE "TBL_MER"."PROCEDURE_GRANT_PRIV"
IS
    BEGIN

    FOR tab IN (SELECT table_name
            FROM   all_tables
            where owner = USER
            order by table_name) LOOP
          EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||tab.table_name||' TO TST';          
      END LOOP;
      COMMIT;
   END;
Andrew
  • 3,632
  • 24
  • 64
  • 113

1 Answers1

1

Did you read the documentation on the TRUNCATE command?

Quoting from the 19c SQL Language Reference

Prerequisites

To truncate a table, the table must be in your schema or you must have the DROP ANY TABLE system privilege.

TRUNCATE is not DML.

EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • i already have this priviledge and yes Truncate is not DML but what i want is to use Truncate also in the same procedure.. – Andrew Oct 05 '21 at 15:25
  • _"but what i want is to use tuncate also in the same procedure"_ I'm not sure what you are asking .. do you want to grant the privilege or do you want to actually truncate a table? If the latter, why would you want to be doing that - in a procedure that is otherwise only granting privileges? If the former - you just want to grant the privilege, then add the necessary GRANT statement, I don't see the difficulty. But then I wouldn't be using a stored procedure to do what is just a one-off operation. Just use sql to generate a sql script. Sanity check the resulting script; execute it. – EdStevens Oct 05 '21 at 19:12
  • yes i got it now...can you also please put into answer the one off operation example ... – Andrew Oct 06 '21 at 07:17
  • Well, I don't know what it is you "got". I pointed out two conflicting requirements and asked for clarification, which you have not provided. I also pointed out that you only need to add another command to your procedure, depending on which of the two conflicting requirements you need. Surely you can add a simple GRANT or TRUNCATE command . . . . – EdStevens Oct 06 '21 at 14:16
  • yes i already added another command in procedure and its working now.. – Andrew Oct 06 '21 at 15:04