Re: Select Privileges
From: ExecMan <artmerar_at_yahoo.com>
Date: Mon, 21 Nov 2011 11:11:09 -0800 (PST)
Message-ID: <73957174-1b4e-4a69-ac0d-ba2f4910807b_at_o1g2000vbe.googlegroups.com>
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
Date: Mon, 21 Nov 2011 11:11:09 -0800 (PST)
Message-ID: <73957174-1b4e-4a69-ac0d-ba2f4910807b_at_o1g2000vbe.googlegroups.com>
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. Received on Mon Nov 21 2011 - 13:11:09 CST