I would like to recreate the public database links with new username and password. Prior to that, I want to know which user the current public database link in connecting to.
The dba_db_links table shows only the user name for private database links. Where can I find the username for public database link.
SQL> create public database link abc.domain.com connect to targetuser idenitified by targetpassword using 'abc.doman.com';
Where I find the information about targetuser; it is not shown in dba_db_links table for public database links.
The oracle version is 18.104.22.168.
The dba_db_links table has columns userid and password. For the public database links, the userid and column values are null. It shows the proper userid for private database links.
I checked the sys.link$ view as well, it is the same case like dba_db_links.
This indicates that the connection information (username/password) was not specified when the database link was created.
This implies that if user A on the local database tries to use this link, then the same account (i.e. A) with the same password exists on the remote database - i.e. all of the users on the local database that use this link are duplicated on the remote database as well.
Thanks again, Srini.
Yeah, appears like the username was not specified when creating the db link.
That means, if such a public database link exists in database DB1 connecting to DB2,
user A@DB1 can read/write from user A@DB2
user B@DB1 can read/write from user B@DB2
This could be a bad design in the security part.
Yes - it is not necessarily a bad design. It simply is an easy way of defining one public database link rather than many private database links ( A --> A, B --> B, etc). If a schema exists only on the local database (and does not exist on the remote database) then it will be unable to use this database link