Oracle 11gR2：以ADMIN_USER身份为REGULAR_USER创建私有dblink的可行性与权限配置咨询

Yes, this is fully achievable. Let's break down the required permissions and the step-by-step process to make this happen.

Permissions You'll Need #

First, let's clarify who needs what:

• ADMIN_USER: You'll need the CREATE ANY DATABASE LINK system privilege. This lets you create private database links in any user's schema—perfect for deploying on behalf of REGULAR_USER. (Chances are this is already granted to your admin account via a role like DBA, but it's worth verifying.)
• REGULAR_USER: Technically, no special system privilege is required to own the private DB link we'll create. However, to use the link (like querying remote tables), they'll need the standard CREATE SESSION privilege (usually granted by default to regular users) and the remote database user specified in the link must have the necessary object permissions (e.g., SELECT on the remote tables they need to access).

Step-by-Step Deployment (As ADMIN_USER) #

1. Confirm/grant the admin privilege (if not already set):

   GRANT CREATE ANY DATABASE LINK TO ADMIN_USER;
   

   If this throws an error, check if the privilege is already granted via a role with:

   SELECT * FROM DBA_SYS_PRIVS 
   WHERE GRANTEE = 'ADMIN_USER' AND PRIVILEGE = 'CREATE ANY DATABASE LINK';
   

2. Create the private DB link in REGULAR_USER's schema:
   Use the fully qualified schema name to ensure the link belongs to REGULAR_USER:

   CREATE DATABASE LINK REGULAR_USER.MY_PRIVATE_DBLINK
   CONNECT TO REMOTE_DB_USER IDENTIFIED BY REMOTE_DB_PASSWORD
   USING 'REMOTE_TNS_ALIAS';
   

   • Swap MY_PRIVATE_DBLINK with your preferred link name.
   • REMOTE_DB_USER/REMOTE_DB_PASSWORD are the credentials for the remote Oracle database.
   • REMOTE_TNS_ALIAS is the entry from your tnsnames.ora file that points to the remote DB.

3. Verify the link was created correctly:
   Run this query to confirm the link is owned by REGULAR_USER:

   SELECT owner, db_link, username, host
   FROM dba_db_links
   WHERE owner = 'REGULAR_USER' AND db_link = 'MY_PRIVATE_DBLINK';
   

   You should see the details of your new private link here.

4. Test the link (optional but recommended):
   Switch to REGULAR_USER and run a quick test to make sure everything works:

   CONNECT REGULAR_USER/REGULAR_USER_PASSWORD;
   -- Replace REMOTE_TABLE with a table the remote user has access to
   SELECT * FROM REMOTE_TABLE@MY_PRIVATE_DBLINK;
   

Quick Tips #

• Private DB links are only accessible to their owner (REGULAR_USER) by default—you don't have to worry about other users accessing it unless you explicitly grant permissions with GRANT SELECT ON DATABASE LINK ....
• For security best practices, avoid hardcoding passwords in deployment scripts. Instead, use an Oracle Wallet to store the remote credentials securely.
• Double-check that your tnsnames.ora (on the database server) has the correct REMOTE_TNS_ALIAS configuration—this is a common point of failure.

内容的提问来源于stack exchange，提问作者Roman