Hi, I'm trying to get the following scenario to work. Maybe someone
can help?

We have the following small setup:
DATABASE: DB1
USERS: TOM & BILL, CHRIS, MARK, JOE

DATABASE: DB2
USERS: TOM

There is a trigger on a table in Tom's schema in DB1. That trigger
will insert data into a table in Tom's schema on DB2, over a DB link.

I want to grant SELECT access to CHRIS only so he can query the table
in Tom's schema on DB2. I do not anyone else to be able to select
from the table.

How can this be done, since the dblink logs in under a given user
account? Can permissions somehow be given to use of database links?
Or by source user?

Thanks

On Nov 17, 12:16*pm, ExecMan <artme... (AT) yahoo (DOT) com> wrote:
No. You could create a read-only user in DB2 that can select from
Tom's table then create a private db link in CHRIS' schema using that
read-only user so he, and only he, can access that object.


David Fitzjarrell

Am 17.11.2011 21:16, schrieb ExecMan:

In DB1, schema TOM, create a view which SELECTs * FROM Tom's table (AT) DB2 (DOT) Let
Tom grant SELECT privilege for that view to Chris. In DB1, schema CHRIS,
create a private synonym pointing to Tom's view, named as the original table
from TOM in DB2.

Regards
Peter

--
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain

On Nov 17, 10:31*pm, ddf <orat... (AT) msn (DOT) com> wrote:

David,

Can one have INSERT but not SELECT privileges? Meaning that in DB1,
Tom inserts into the table on DB2. However, that is all he can do,
insert. Then I get up what is necessary to limit select access. Or,
does one automatically get select privileges if they can insert also?

On Nov 21, 9:08*am, ExecMan <artme... (AT) yahoo (DOT) com> wrote:
As the grants go select, insert, update, delete are all separate
privileges; I would expect that one could have insert-only privileges
on a table given that information (I don't currently have a database
to test that on). I don't know what good that would do since inserted
values could not be verified by the user performing the inserts, but
it certainly appears to be possible,


David Fitzjarrell

On Nov 17, 10:31*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Dave,

I've got most things in place, but there is one small strange thing
happening.

Here is how I did the set up:

DATABASE: DB1
USERS: TOM, BILL

DATABASE: DB2
USERS: TOM, BILL, JIM

The actual table will exist in the JIM schema on db2. Synonyms are
used to reference the object in JIM's schema.

On DB1, TOM will be able to INSERT into the table on DB2. I've
created a private DB link granting INSERT privileges.
On DB1, BILL will be able to SELECT from the table on DB2. I've
created a private DB link granting SELECT privileges.

I can perform these operations fine from DB2. The privileges work as
they should. TOM can only INSERT into the table and BILL can only
SELECT from it.

However, from over the DB Link, the insert fails with 'insufficient
privileges'. Is there something I am missing from going over the DB
Link and objects not specifically owned by you?

On Nov 21, 12:17*pm, ddf <orat... (AT) msn (DOT) com> wrote:

I read that you cannot have insert-only privileges over a DB link
because Oracle performs a 'read' against the table, that you need
select privileges also.

What kind of read is it doing? I'm just curious. Once I granted
select privileges, everything is working. But, it sort of negates the
'insert only' account.

On Nov 21, 11:11*am, ExecMan <artme... (AT) yahoo (DOT) com> wrote:
Speculation here.

I think this derives from the distributed database logic for 2-phase
commit. Transactions in such a case have a coordinator, which would
normally be the initiating transaction. If anything stops any part of
the databases involved from committing a transaction, Oracle will try
to rollback all the transactions involved. Any that can't be will be
"in doubt," and an admin will have to manually handle it. Oracle
wants to minimize the possibility of having to do this. So my
speculation is, the select is necessary to know right up front whether
a transaction can happen. I know, that seems like a weak
rationalization for a distributed query. Perhaps it has something to
do with reconciling with the pending transactions table - a
transaction starts when you modify data, so Oracle is being optimistic
that you are preparing for a 2-phase. On the other hand, it might
just be needing to read the interested transaction list. A trace
would probably be informative.

What exactly were you reading?

jg
--
@home.com is bogus.
http://allthingsd.com/20111118/hps-i...nd-at-bernies/