DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle permission for snapshot

  1. #1
    Join Date
    Jul 2008
    Posts
    2

    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!

  2. #2
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    So you are able to do it from the TOAD

  3. #3
    Join Date
    Jul 2008
    Posts
    2
    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;

  4. #4
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Gud

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width