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@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" <[EMAIL PROTECTED]> > 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" <[EMAIL PROTECTED]> > > 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@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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).