This discussion is archived
6 Replies Latest reply: Mar 4, 2013 5:13 AM by Hoek RSS

run procedure as another user

marco Newbie
Currently Being Moderated
Hi all,

how do I run procedure as another user?
  • 1. Re: run procedure as another user
    jeneesh Guru
    Currently Being Moderated
    What do you mean by "run as another user'?

    You can simply log in as another user and run, right? (schema_name.procedure_name(....); )

    Or are you talking about AUTHID DEFINER clause? If you define the procedure as "AUTHID DEFINER", it will always run with the rights of the creator of the procedure..

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574
  • 2. Re: run procedure as another user
    Paul Horth Expert
    Currently Being Moderated
    If the procedure is owned by user A, for example, and you are user B,
    then just prefix the schema name:
    begin
     A.proc_name;
    end;
    This assumes user B has been granted EXECUTE access to that procedure.
  • 3. Re: run procedure as another user
    Hoek Guru
    Currently Being Moderated
    Just connect as that 'another user' and run the procedure?

    If that isn't what you're looking for then explain in more details (database version, etc) please.
    See:
    {message:id=9360002}

    edit
    In addition to the other mentioned options:
    You might want to check out CURRENT_SCHEMA.

    "The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.

    This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name."
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2013.htm#SQLRF53050

    Edited by: Hoek on Mar 4, 2013 1:44 PM
  • 4. Re: run procedure as another user
    Rahul_India Journeyer
    Currently Being Moderated
    JUST USE THIS
    begin
    other_schema.proc_name;
    end;
    
    
    where
    
    proc_name is a Procedure in your other_schema
    jeenesh edited lol
  • 5. Re: run procedure as another user
    jeneesh Guru
    Currently Being Moderated
    Rahul India wrote:
    JUST USE THIS
    begin
    current_schema.proc_name;
    end;
    current_schema..?

    Or other_schema?
    :)
  • 6. Re: run procedure as another user
    Hoek Guru
    Currently Being Moderated
    Another good explanation:
    http://www.oracle-base.com/articles/misc/schema-owners-and-application-users.php

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points