I'm creating a database link that is not public (with "CREATE DATABASE LINK" instead of "CREATE PUBLIC DATABASE LINK") with a user login that has no administration privileges. This works well and the link can be used without problems.
However, I find some strange behavior:
-When I query the available links as an administrator (with "SELECT * FROM V$DBLINK"), the created link is not listed (the table returned by the query is empty). This works well if the link is created as PUBLIC instead. The database link is listed if I execute the query "SELECT * FROM V$DBLINK" with the same user that created the link. Even if the link is not public, shouldn't it be visible also for the administrator (at least, if not for using it, for the case when the administrator wants to know which database links exist)?
-I find it strange that I need to provide drop privileges to drop a public database link ("GRANT DROP PUBLIC DATABASE LINK TO ...") but this is not the case for private database links ("GRANT DROP DATABASE LINK TO ..." gives an error, and it seems that the "CREATE DATABASE LINK" privilege is enouh for both creating and dropping private links).
-Is there any way for a normal (i.e., non-admin) user to list the database links that he has created? The sentence "SELECT * FROM V$DBLINK" is executable for the administrator, but that view is not accesible to a non-admin user.
Thank you for any tips and suggestions!!
See USER/ALL/DBA_DB_LINKS - usual granularities apply.
As per most privileges, if you have a privilege to create your own TABLE/VIEW/PROCEDURE/DB LINK/ETC then you can drop what you have created.
But the scope of a public database link is larger therefore special privilege is required.
Please note the difference between v$dblink & dba_db_links is as follows:
V$DBLINK:This view describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK
DBA_DB_LINKS:DBA_DB_LINKS describes all database links in the database. Its columns are the same as those in ALL_DB_LINKS.
You also can use user_db_links to list the database links created by normal user. And drop privileges are not required to drop the user created objects, because if you have privileges to create the object then you can drop that object without any additional privileges.