Insufficient Privileges

A common question asked by developers is why doesn't DML or DDL that they can successfully run from the SQL> prompt work when they put it into a procedure. The simple answer to this is that privileges granted via a role are not enabled in a procedure. For a full explanation, have a look at Thomas Kytes article.

However, I recently had a similar question from a developer who was attempting to create a snapshot via DBMS_SQL. The developer had the 'create snapshot' privilege directly but still could not create the snapshot.

A quick trace reveals the answer. Since creating the snapshot also creates an underlying table and view, your developer will also need this privileges. Thus, if your developers are getting "ORA-01031: insufficient privileges" when they dynamically create a snapshot, issue the following

SQL> grant create snapshot,
  2        create table,
  3        create view
  4     to developer;