Kathy,
Something is fundamentally wrong with the way things are setup
in that database, but it's not something easily troubleshot via email.
There are *many* ways to setup a distributed system. Some are rather
complex to setup, but make development easier. Others are more
secure, and even more difficult to design and setup.
I guess the point is, there are many ways to do it. Get a copy of Charles
Dye's book ( O'Reilly of course ) on Oracle Distributed Systems.
Here's a set of 5 simple scripts that demonstrate a simple distributed
system. Ok, *very* simple. Try it and see if it will work for you. Then
compare what's done here to what is being done in your system.
I know these scripts work, cuz I just wrote them. They are named
1-5[p or a].sql. Run them in numeric order, from the system indicated.
Run 1a.sql first from the archive system, 2p.sql next from production, etc.
You have a lot of homework ahead of you. 8)
Jared
================================================================
-- 1a.sql
-- create archive user
connect system
create user archive identified by archive
default tablespace users
temporary tablespace users
/
grant connect, resource to archive;
connect archive/archive
drop table mytab_archive;
create table mytab_archive ( test_date date, arch_date date );
================================================================
-- 2p.sql
-- create prod user
connect system
create user prod identified by prod
default tablespace users
temporary tablespace users
/
grant connect, resource to prod;
================================================================
-- 3p.sql
-- create prod tables and trigger
connect prod/prod
drop database link archive;
create database link archive
connect to archive
identified by archive
using 'ifsdev'
/
drop table mytab;
create table mytab ( test_date date );
create or replace trigger mytab_biudt
after insert or update or delete
on mytab
for each row
declare
v_test_date mytab.test_date%type;
begin
if inserting then
v_test_date := :new.test_date;
elsif updating then
v_test_date := :old.test_date;
elsif deleting then
v_test_date := :old.test_date;
end if;
insert into mytab_archive@archive( test_date, arch_date)
values( v_test_date, sysdate);
end;
/
show error trigger mytab_biudt
================================================================
-- 4p.sql
insert into mytab values(to_date('01/01/2002','mm/dd/yyyy'));
commit;
update mytab set test_date = test_date + 3;
commit;
================================================================
-- 5a.sql
select * from mytab_archive
/
================================================================
Kathy Duret
<KathyD@belkin To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
.com> cc:
Sent by: Subject: RE: FW: insert privilege across
db link
[EMAIL PROTECTED]
om
01/04/02 09:26
AM
Please respond
to ORACLE-L
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@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
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
