RE: FW: insert privilege across db link

Ok, I thought I had it but it is still not working.

Production user on Prod has dba privileges, has been granted insert on any table and granted insert to all the archive user tables. He can see the view in Archive user in Prod and can see the tables in Archive User in the Archive Database;

Created views (and tried synonyms) on all the Archive User tables in the Archive database in archive user in the Prod.

Archive user in the PROD database has dba privileges and created a public database link to the ARCHIVE database. (also tried creating the public database link as prod user)

I am still getting insufficient privileges when I try to create my insert trigger on the Prod user in Prod.

I tried using synonyms instead of views that didn't work either.

What am I missing?

Kathy

-----Original Message-----
Sent: Friday, January 04, 2002 2:10 AM
To: Multiple recipients of list ORACLE-L

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).

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kathy Duret
  INET: KathyD_at_belkin.com

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).