Oracle permission for snapshot

Hi!

I'm trying to create a snapshot using a dynamic command and the sentence:
EXECUTE IMMEDIATE command;

I need to do it this way cause i have a procedure that creates the snapshots, so it's necessary to use dynamic commands.

I've a problem cause oracle says I haven't enough privileges to do so, but if I create the command in the Toad editor directly, it works perfectly and permissions aren't a problem to create my snapshot.

The table I need to create the snapshot is already in the same schema, so it isn't a problem.

The user has these privileges:
- ALTER/CREATE/DROP/ ANY SNAPSHOT
- ON COMMIT REFRESH
- SELECT ANY TABLE
- UNLIMITED TABLESPACE

Thank you all!

So you are able to do it from the TOAD :)

No, i'm not able to do it from a procedure, this is what i wan to.

But....i've found the permission i need, if anyone needs it:

EXECUTE ANY PROCEDURE

With this permission you allow your user to create or execute a dinamyc command in a procedure which creates a new snapshot view this way:

IN A PROCEDURE :
------------------
v_command := 'CREATE MATERIALIZED VIEW ..... ';

EXECUTE IMMEDIATE v_command;

Gud :)

Quote:

---

Originally Posted by emma

No, i'm not able to do it from a procedure, this is what i wan to.

But....i've found the permission i need, if anyone needs it:

EXECUTE ANY PROCEDURE

With this permission you allow your user to create or execute a dinamyc command in a procedure which creates a new snapshot view this way:

IN A PROCEDURE :
------------------
v_command := 'CREATE MATERIALIZED VIEW ..... ';

EXECUTE IMMEDIATE v_command;

---

that will also give your use the ability to execute any procedure in the database - not a good thing at all