title: Execute dynamic DDL in PL/SQL procedure through definer role permissions
body: <p>I want to perform some dynamic DDL in a procedure owned by an admin user. I'd like to execute this procedure with a technical operational user with definer rights (operational user doesn't have the create table role).</p>
  
  <p>The problem is the 'create table' permission is granted to the admin user through use of a role, which doesn't allow me to execute the DDL as it seems that roles don't count in named pl/sql blocks.</p>
  
  <pre><code>create or replace
  PROCEDURE test_permissions AUTHID DEFINER AS
    v_query_string VARCHAR2(400 CHAR) := 'CREATE TABLE TEST(abcd VARCHAR2(200 CHAR))';
  BEGIN
  
    EXECUTE IMMEDIATE v_query_string;
  
  END;
  </code></pre>
  
  <p>What I tried:</p>
  
  <ul>
  <li>Running the procedure from a function set to AUTHID DEFINER with AUTHID CURRENT_USER on the proc (hoping the function would cascade the definer somehow) </li>
  <li>Putting an anonymous block inside the function to execute the DDL (as roles seem to count in anonymous block)</li>
  </ul>
  
  <p>If I set the AUTHID to CURRENT_USER, I can execute the procedure correctly with the admin user.</p>
  
  <p>Is there any way I can work around this without granting CREATE TABLE directly to the admin user?</p>