4

When trying to place a GRANT statement in an Oracle 11 stored procedure, it reports that GRANT is an unexpected symbol. Does GRANT need to be prefaced by something, or does Oracle simply disallow running GRANTS inside SPs?

Spain Train
  • 5,890
  • 2
  • 23
  • 29

2 Answers2

5

It's a bad idea to use DDL (like GRANT) inside stored procedures.

You will have to use dynamic SQL (EXECUTE IMMEDIATE) to do this, but, honestly, I don't see why would you want to do this inside a stored proc.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • And as always, a side effect one should be aware of is that DDL performs an implicit COMMIT. – DCookie Mar 22 '11 at 16:09
  • @DCookie: yes and `1525` more side effects which I tried to generally describe as "bad idea". – Quassnoi Mar 22 '11 at 16:11
  • It is for an administrative script that runs dbms commands that much permissions, not to be run often or by more than one user. Is there a preferable way to solve such a problem? – Spain Train Mar 22 '11 at 17:50
  • @MikeS: sure, just run it as a plain query, without wrapping it into a stored proc. – Quassnoi Mar 22 '11 at 17:52
2

Here's a PL/SQL stored procedure that grants object privileges (SELECT, UPDATE, etc.) on all tables owned by the current user to another user, e.g. - "appuser". 

CREATE OR REPLACE PROCEDURE grant_privs
IS
   CURSOR ut_cur IS SELECT table_name from user_tables;
   ut_rec ut_cur%rowtype;
BEGIN
   FOR ut_rec IN ut_cur
   LOOP
      EXECUTE IMMEDIATE 'GRANT ALL ON ' || ut_rec.table_name || ' TO appuser';
   END LOOP;
END;
/

It was executed automatically after deploying database changes for a web application. The current user owns the database tables. After deploying any database changes, the stored procedure was executed to ensure that "appuser" could run SQL statements on all of the tables. The web application connected as "appuser", which had only limited system privileges, for security reasons.

This is both a solution to the problem and a solid use case for the solution.

David Keener
  • 520
  • 6
  • 7