Privileges Database Link
in [Oracle Help]
|
Prev: Any book on Oracle DBMS's internals?
Next: Oraqcle RAC configuration question. Nodes on Unix, ASM on Windows?
From: The Magnet on 24 May 2010 14:18 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 john(a)comm.zacks.com; grant select on status to john(a)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.
From: Shakespeare on 24 May 2010 16:49 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 john(a)comm.zacks.com; > grant select on status to john(a)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
From: The Magnet on 24 May 2010 16:52 On May 24, 3:49 pm, Shakespeare <what...(a)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...(a)comm.zacks.com; > > grant select on status to j...(a)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.
From: joel garry on 24 May 2010 17:20 On May 24, 1:52 pm, The Magnet <a...(a)unsu.com> wrote: > On May 24, 3:49 pm, Shakespeare <what...(a)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...(a)comm.zacks.com; > > > grant select on status to j...(a)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 -- @home.com is bogus. How stupid are Republicans? http://www.10news.com/news/23651893/detail.html
From: Mark D Powell on 26 May 2010 10:28
On May 24, 5:20 pm, joel garry <joel-ga...(a)home.com> wrote: > On May 24, 1:52 pm, The Magnet <a...(a)unsu.com> wrote: > > > > > > > On May 24, 3:49 pm, Shakespeare <what...(a)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...(a)comm.zacks.com; > > > > grant select on status to j...(a)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 > -- > @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 -- |