username/password for database link
Rao, May 24, 2002 - 12:17 pm UTC
Hello Tom,
" create public database link foo
using 'foobar'; "
U told to create the database link like the above but, I guess we need to give the username/password for the username in the dtabase we are connecting to i.e
we should create the private database link like this
create database link connect to
PUBLIC_READ identified by password using 'foobar';
This is my way of thinking as we are connecting to foobar and dont we need to give the username/password? I might be wrong .Please verify..
Thanks
May 24, 2002 - 8:07 pm UTC
please re-read the answer.
I described the two types -- a PROXY link that includes the username/password and a non-proxy link that does NOT include that. We discussed the ins and outs of each type.
Reader
A reader, May 24, 2002 - 12:22 pm UTC
"
If you use a non-proxy link, then that user must exist on the remote site and
will have ONLY THE PRIVS associated with their account.
"
Why does Oracle requires same password also in local and
remote databases
May 24, 2002 - 8:10 pm UTC
because there is no mechanism to ask you for the password for the remote site -- and we would NEED to (else consider the SYS and SYSTEM accounts, you could just install Oracle on your PC and connect as SYS or SYSTEM in any database if we just "trusted" you were who you said you were)
Database Links
Munz, May 24, 2002 - 9:22 pm UTC
Tom:
By creating a user 'PUBLIC_READ' are you implementing the 1st approach (proxy links) and having everyone login in using it.
create public database link foo
connect to PUBLIC_READ
identified by password
using 'foobar'
Is this correct?
May 25, 2002 - 9:03 am UTC
Yes, based on this "If you want users (all users) on server2 to select against table T on PROD -- I might do this:"
that created a proxy link, accessible to everyone, that conveys the privs of the PUBLIC_READ user.
Snappy Tom does it again
Patrick Saunders, September 03, 2003 - 7:18 pm UTC
In his usual succint manner, Tom details the specifics steps needed to create database links, highlighting the two types PROXY and NON-PROXY.
Tom also details the security need for the user to have same password in the local and remote database when using NON-PROXY database links.
In the next paragraph Tom slaps a reader around the ears for being lazy. Go Tom!
DB Link security issue.
Pritesh Jani, July 15, 2004 - 4:59 am UTC
If I am having DB link and done some DML operations. After that I have done some DML opearation on the local database. I haven't commited the data yet. Then DB Link goes down. What will happen?
In my case from local database if i am issuing
select * from emp@iasdb
then it shows updated data.
If i am connecting to target database then it shows non updated data. How it is possible?
What happen when DB Link goes down? Which database (taget / local) will keep lock of tables / rows I am updating?
Tom can you please help me.
July 15, 2004 - 12:24 pm UTC
if you connect to the target database when "all is well" you could not see it either -- changes are not visible to other sessions until you commit.
if the dblink goes down and neither side detects it (tcp doesn't tell us), then the data is locked on both sides -- when you commit -- you would recieve an error, and rollback (local data is "unlocked"). the other side -- upon detecting the link is broken would act just like "a pc getting turned off" and would roll back in due time as well.
think of the local database as your "pc" and the remote database as the "database" -- it would be the same.
A reader, April 05, 2006 - 4:29 pm UTC
To "A reader" : some options
J. Laurindo Chiappa, July 26, 2017 - 7:23 pm UTC
See, the database link is just a connection to another database : Connor is right, no sense in forbidding connections... Maybe someone heard that passwords are visible in clear-text inside the database for dblinks (it was true in very very old versions/releases, from 10gR2 iirc it is simply not true) but for fear of the unknow all the dblink were banned, for no reason in the case of recent/modern versions.... No sense (and nonsense) at all...
Anyway, if dblinks are out of order and you need to 'dig without a showel', ie, work without the correct tool, you can try :
a) execute the COPY command in SQL Developer (running via script, with F5 key) or in SQLcl , both iirc don´t have the same line limit as sqlplus
b) write a Java stored procedure that connect in the remote database without database link and do the needed actions
And let me say : BOTH options imho are much much LESS secure than dblinks, because you will type the user/password and destination db host and port - in a dblink all these things exists but they are encripted inside link$ and other system tables/views...
Regards,
J. Laurindo Chiappa
July 27, 2017 - 1:19 am UTC
nice input.