1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

AUTHID CURRENT_USER behavior

Discussion in 'SQL PL/SQL' started by prashant, Jan 16, 2009.

  1. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Hi Guys,

    I was going through the following link which says about AUTHID CURRENT_USER :-

    http://www.adp-gmbh.ch/ora/plsql/authid.html

    It works when i do it among 2 schemas in the same instance. That is if i call the procedure(with AUTHID CURRENT_USER) of schema A from schema B the changes are done to tables of B instead of A(if the same table exists).

    On the other hand if i try to do it among schemas of different instances via db-link, say executing proc in schema A from schema B, the changes are done to tables of A only.

    Why is this difference? Also is there a way to accomplish the same via db-link as we accomplished in inter schema within same instance?

    Regards,
    Prashant
     
    gurujothi likes this.
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Prashant, i am not a dba expert but for your proc in schema A to make changes to tables of schema B, shouldn't your DML queries of proc in schema A mention the db link? else the query will always find the table in schema A. That's why the changes are done to tables of A only.
     
  3. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Thank you Sadik for your reply.

    See the requirement here is:-

    Say i have 2 schemas 'A' & 'B' on 2 different instances. Schema B is connected to schema A via DB-Link.

    Say both the schemas have an EMP table. Now i create a procedure EMP_UPD in schema A (AUTHID CURRENT_USER) which updates SAL column of EMP table.

    Now from schema 'B' if i execute EMP_UPD@dblink, it updates the EMP table of schema 'A' which is not what i expected or desired.

    In the other case i.e both the schemas are in the same instance and I execute A.EMP_UPD from schema 'B' it updates the EMP table of schema B.

    I dont mind keeping the same tables across schemas(in diff instances) but i want to keep only 1 central copy of the procedure. This is possible across multiple schemas within same instance, but between schemas among different instances is what i am trying to acheive and not able to :(

    You may link this requirement with the one mention in the Server Administration->Replicating Packages thread. I posted it here as it was related to PL/SQL concepts.

    Thanks & Regards,
    Prashant
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your procedure does not reference any table in Schema B on the remote server, so you cannot update your 'local' table by a call to a remote procedure. You'll need to rewrite this code to accept a remote database name and append that information to the update statement, making your statement use dynamic sql.
     
  5. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Thank you Zargon for your reply..

    In the case where i have both the schemas in the same instance, i am able to do so using AUTHID CURRENT_USER.

    Can you please elaborate a bit more on that?

    Thanks..
    Prashant
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And that is a LOCAL privilege. It doesn't transfer to the remote database.


    You'll need to write a dynamic SQL statement to update records on a remote database by referencing the remote object in your local call:
    Code (Text):

    create or replace procedure myupdateproc(tname in varchar2, colname varchar2, colval varchar2, remote_srvr varchar2 default NULL) is
    ...
    if remote_srvr is not null then
         sqlstmt:='update '||tname||'@'||remote_srvr||' .....";
    else
         sqlstmt:='update '||tname||' set ...';
    end if;

    execute immediate sqlstmt;
    ...
     
    Such a statement would update the remote object.


    David Fitzjarrell
     
    gurujothi likes this.
  7. prashant

    prashant Forum Advisor

    Messages:
    85
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    New Delhi, India
    Thank you David for your reply. I am sure this info will be quite useful. Will try the same and get back incase of any issues..

    Thanks once again.

    Regards,
    Prashant