6 Replies Latest reply: Mar 4, 2013 7:13 AM by Hoek RSS

    run procedure as another user

    marco
      Hi all,

      how do I run procedure as another user?
        • 1. Re: run procedure as another user
          jeneesh
          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
            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
              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
                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
                  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
                    Another good explanation:
                    http://www.oracle-base.com/articles/misc/schema-owners-and-application-users.php