Re: Privileges Database Link
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 26 May 2010 07:28:00 -0700 (PDT)
Message-ID: <ad801c87-ac79-49cc-a2f7-c7ce032dbecb_at_v18g2000vbc.googlegroups.com>
On May 24, 5:20 pm, joel garry <joel-ga..._at_home.com> wrote:
> On May 24, 1:52 pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On May 24, 3:49 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
> > > Op 24-5-2010 20:18, The Magnet schreef:
>
> > > > We have 2 database which have DB links between. The user in both
> > > > database have the same name. We cannot figure out how to grant the
> > > > privileges.
>
> > > > SQL> grant select on status to j..._at_comm.zacks.com;
> > > > grant select on status to j..._at_comm.zacks.com
> > > > *
> > > > ERROR at line 1:
> > > > ORA-00933: SQL command not properly ended
>
> > > > SQL> grant select on status to john;
> > > > grant select on status to john
> > > > *
> > > > ERROR at line 1:
> > > > ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
>
> > > > Help please.
>
> > > Your database link connects through a user in the target database.
> > > Grants should be done IN the target database by an account with the
> > > privileges to do so. You can not grant over the link.
>
> > > Shakespeare
>
> > Right, but take this:
>
> > DB1
> > ------
> > USER: JOHN
> > TABLE: TAB1
>
> > DB2
> > ------
> > USER: JOHN
> > TABLE: TAB1
>
> > I cannot grant a privilege to myself. I mean, the user names are the
> > same, and I am having trouble with that part, unless I am missing
> > something.
>
> When you attach to the dblink, you login as that user. So you don't
> need to grant anything to that user. The fact that users in two
> databases are named the same is irrelevant. What are you trying to
> accomplish that isn't working, besides this granting?
>
> jg
> --
> _at_home.com is bogus.
> How stupid are Republicans?http://www.10news.com/news/23651893/detail.html- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 26 May 2010 07:28:00 -0700 (PDT)
Message-ID: <ad801c87-ac79-49cc-a2f7-c7ce032dbecb_at_v18g2000vbc.googlegroups.com>
On May 24, 5:20 pm, joel garry <joel-ga..._at_home.com> wrote:
> On May 24, 1:52 pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
> > On May 24, 3:49 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
> > > Op 24-5-2010 20:18, The Magnet schreef:
>
> > > > We have 2 database which have DB links between. The user in both
> > > > database have the same name. We cannot figure out how to grant the
> > > > privileges.
>
> > > > SQL> grant select on status to j..._at_comm.zacks.com;
> > > > grant select on status to j..._at_comm.zacks.com
> > > > *
> > > > ERROR at line 1:
> > > > ORA-00933: SQL command not properly ended
>
> > > > SQL> grant select on status to john;
> > > > grant select on status to john
> > > > *
> > > > ERROR at line 1:
> > > > ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
>
> > > > Help please.
>
> > > Your database link connects through a user in the target database.
> > > Grants should be done IN the target database by an account with the
> > > privileges to do so. You can not grant over the link.
>
> > > Shakespeare
>
> > Right, but take this:
>
> > DB1
> > ------
> > USER: JOHN
> > TABLE: TAB1
>
> > DB2
> > ------
> > USER: JOHN
> > TABLE: TAB1
>
> > I cannot grant a privilege to myself. I mean, the user names are the
> > same, and I am having trouble with that part, unless I am missing
> > something.
>
> When you attach to the dblink, you login as that user. So you don't
> need to grant anything to that user. The fact that users in two
> databases are named the same is irrelevant. What are you trying to
> accomplish that isn't working, besides this granting?
>
> jg
> --
> _at_home.com is bogus.
> How stupid are Republicans?http://www.10news.com/news/23651893/detail.html- Hide quoted text -
>
> - Show quoted text -
JG has nailed the issue on the head. If the link is defined without a fixed username and password in the link then it would be known as a current user link and Oracle would expect the same username to be defined in the remote database with the same password. In other words Oracle would expect the usernames to be the same user with an account on both databases.
When a fixed user link is used then every connection is as the remote database username contained in the link.
There are also global links, which use LDAP security, and these are basically just another form of current user link where the current user is LDAP authenicated to the database.
HTH -- Mark D Powell -- Received on Wed May 26 2010 - 09:28:00 CDT