Oracle permission for snapshot
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;
Originally Posted by emma
that will also give your use the ability to execute any procedure in the database - not a good thing at all
Click Here to Expand Forum to Full Width