Re: Select Privileges
Date: Mon, 21 Nov 2011 14:47:02 -0800 (PST)
Message-ID: <4b9dd820-d7bd-47e0-8d7a-7454521dce60_at_v31g2000prg.googlegroups.com>
On Nov 21, 11:11 am, ExecMan <artme..._at_yahoo.com> wrote:
> On Nov 21, 12:17 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
> > On Nov 21, 9:08 am, ExecMan <artme..._at_yahoo.com> wrote:
>
> > > On Nov 17, 10:31 pm, ddf <orat..._at_msn.com> wrote:
>
> > > > On Nov 17, 12:16 pm, ExecMan <artme..._at_yahoo.com> wrote:
>
> > > > > 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
>
> > > > 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
>
> > > 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?
>
> > 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
>
> 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.
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
-- _at_home.com is bogus. http://allthingsd.com/20111118/hps-itanium-business-is-like-a-remake-of-weekend-at-bernies/Received on Mon Nov 21 2011 - 16:47:02 CST