| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: FW: insert privilege across db link
You wouldn't believe it.....I didn't have Resource role or DBA role for my #$%* User. This user had DBA privs... so I don't know why/when it was changed.
Unbelievable.... I was trying everything.
A BIG thanks to Babu and Jared for offering alot of help.
Thanks also Michal for the wonderful link that really explained how db links work.
Kathy
-----Original Message-----
Sent: Friday, January 04, 2002 12:21 PM
To: ORACLE-L_at_fatcity.com
Cc: KathyD_at_belkin.com
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
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 );
connect system
create user prod identified by prod
default tablespace users
temporary tablespace users
/
grant connect, resource to prod;
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_at_archive( test_date, arch_date) values( v_test_date, sysdate);
end;
/
show error trigger mytab_biudt
insert into mytab values(to_date('01/01/2002','mm/dd/yyyy')); commit;
update mytab set test_date = test_date + 3; commit;
select * from mytab_archive
/
Kathy Duret
<KathyD_at_belkin To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
.com> cc:
Sent by: Subject: RE: FW: insert privilege across
db link
root_at_fatcity.c
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_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
>
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).Received on Fri Jan 04 2002 - 17:51:22 CST
 |
 |