Re: FW: insert privilege across db link

I think that it depends on type of database link you created. CONNECTED_USER versus CURRENT_USER.
Imagine two users - user "A" in database "A" and user "B" in database "B". User "B" wants to connect to database "A" using link and do something...

Now: If the database link is of type CONNECTED_USER then user "B" connects to database "A" as /connected_/ user "B" so the rights must be done for user "B".

     If the database link is of type CURRENT_USER then user "B" connects to database "A" as a user who owns the trigger (in this case as /current_/user "A").

If I understood you problem clearly it will be better to do database link of type CURRENT_USER (your public db link in the example is of type CONNECTED_USER).

Try reading
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76960/ds_admin.htm#24406 Maybe it will be clearer. :-)

HTH,
        Mike

Kathy Duret wrote:
>
> Ok let's start over.....hopefully this will be clearer.
>
> I created a public database link owned by system, system is the user I created the
>database link under.
>
> created in my Production database:
> create public database link ARCHIVE
> connect to system identified by "&repadmin_pass" using 'ARCHIVE';
>
> create public database link PROD
> connect to system identified by "&repadmin_pass" using 'PROD';
>
> User produser which owns the original tables is in the Production database
> User archiveuser owns the archive tables in the Archive database.
>
> Triggers were created under produser in the PROD database so when a delete takes
>place a row is inserted into archiveuser table in the Archive database.
>
> Triggers work fine when A schema tables are in Archive database.
>
> I need to grant some sort of insert permission but how. Grant insert on atable to
>A_at_PROD doesn't work....
> tried to grant insert on all tables to A, didn't work either.
>
> I get a permission error when the triggers are fired
> SQL> @cre_trig
> AFTER DELETE ON atable
> *
> ERROR at line 2:
> ORA-01031: insufficient privileges
>
> Thanks
>
> Kathy
> -----Original Message-----
> Sent: Thursday, January 03, 2002 2:25 PM
> To: Multiple recipients of list ORACLE-L
>
> what is the user in the db link. it it "b" or some other user?
>
> babu
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, January 03, 2002 4:30 PM
>
> > the db link is a public one owned by system.
> >
> > User B is only in Bshema. User A with the trigger is only is Aschema.
> >
> > Kathy
> >
> > -----Original Message-----
> > Sent: Thursday, January 03, 2002 1:16 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > in the db link are you using the same user as the table owner in the
> remote
> > db. in your case are you using the user "b" in your dblink?
> >
> > babu
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, January 03, 2002 3:35 PM
> >
> >
> > > database is 8.1.7
> > >
> > > how do I grant insert privilege to a user in a different database?
> > >
> > > Note: the database link in place in both database and I can describe
> > tables in each just fine. In the trigger I already have the insert into
> > b.address_at_bschema. When I try to execute the
> > > trigger I get insufficient privileges. I have tried the trigger locally
> > and it works just fine it is when I try to insert a row across the
> database
> > link I get insufficient privileges.
> > >
> > >
> > > What am I missing/forgetting?
> > >
> > > Thanks
> > >
> > > Kathy
> > >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Michal Zaschke
  INET: zaschke_at_suas.cz

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).