title: Using an Oracle PL/SQL stored procedure to grant rights on another user's tables
body: <p>I'm having an issue with an application that does the following:</p>
  
  <ul>
  <li>PL/SQL package 'A' contains all functions/procedures for an application</li>
  <li>'A' is owned by 'USER_A'</li>
  <li>'A' creates user accounts in Oracle, and creates tables under those users</li>
  <li>'A' must also be able to TRUNCATE/INSERT to the users' tables</li>
  </ul>
  
  <p>Note - all created tables are within a low security tablespace, let's call it 'MY_TS'.
  Although this package creates the users and tables within the each new user's schemas, 'A' doesn't have the rights to INSERT to these tables, or truncate them. (default in Oracle?)</p>
  
  <p>Two approaches to solve this I am trying are:</p>
  
  <ol>
  <li>Echo a GRANT statement into a command line call to sqlplus to login as the new user and issue a GRANT ALL ON [table] to USER_A, or</li>
  <li>Create a separate procedure (call it 'B') created and owned by an account with DBA rights.  This proc runs with "AUTHID DEFINER" and first checks to make sure the table is in 'MY TS' before running the GRANT ALL ON [table] to USER_A statement</li>
  </ol>
  
  <p>I ran into a roadblock with approach #1.  It's apparently not that easy in Oracle to shell out a command (I have much more exposure to SQL Server where calling to the command shell isn't advised, but is easy if you want to!), although my testing of the command line string I'm using works perfectly.</p>
  
  <p>For approach #2, I wrote a proc 'B' that is defined with the "AUTHID DEFINER" pragma, and is compiled by an account with DBA-rights.  This proc passes the tablespace check (limp security), but returns the error "ORA-01929: no privileges to GRANT" when executing the GRANT statement which is built using dynamic SQL passed into the 'EXECUTE IMMEDIATE' command.</p>
  
  <p>Anyone have any tips for another approach that will work better or a solution to either approach that would work (and pass with the DBA)?  Giving greater blanket rights to USER_A doesn't sound like it's going to be an option.</p>
  
  <p>Thanks for any tips/feedback!</p>
  
  <p>Dan</p>