Deploy materialized view with database link using another user Our database deployment process logs in as sys to deploy database objects.  We ran into a problem when we wanted to deploy a private database link for another user (AU).  Our solution was to:


Create a procedure owned by AU that uses an execute immediate to create the link
Run the procedure
Drop the procedure.  


This worked great!  Unfortunately it did not work when we tried to script the creating of a materialized view owned by AU that uses its private database link.  It appears that it connects to the remote database using the logged in user (sys) not the user that owns the link (AU) and therefore fails:

ORA-04052: error occurred when looking up remote object
AU.Table_Name@Private_Link_Name
ORA-00604: error occurred at recursive SQL level 2
ORA-01017: invalid username/password; login denied
ORA-02063: preceding line from Private_Link_Name
ORA-06512: at "AU.temporary_procedure", line 3
ORA-06512: at line 1


The materialized view creates fine when done by AU directly.  Here is what the procedure looks like:

create procedure AU."tmp_doit_20180509" is 
Begin 
   execute immediate 'create materialized view m1 as select 1 from dual@private_link_name';
End; 
/


The database link does not have credentials defined, so it uses the credentials of the connected user.  When logged in as AU these are the same between databases, but for sys they are different.

Here are some alternatives I have considered:


Manually log in as each user needing to create materialized views with database links - this breaks our automated deployment process.
Use a dbms_scheduler job to create the mview - this also does not work and fails similarly to the procedure method because the job runs in the context of the user that created it even if it is in another schema.
Store credentials for users and login as each specific user to do the deployment - depending on how this is done, it seems that it would either be complex to build to make it secure or complex to maintain, perhaps both.
Connecting as a proxy user - gives the same error.
Here is the solution I am using for now.  I don't like it, but until I find something better, it's the best I can do.


At the start of the deployment a public database link with named credentials is created that uses the same name as the private database link will use.
The materialized view is created and works because it is using the public link with embedded credentials.
A procedure is created owned by "Another User" (AU) that uses an execute immediate to create the private database link.
The procedure is run to create the private database link.
The procedure is dropped.
The public database link is dropped.


Unfortunately this requires the swapping the existence of both the public and private database links before and after any deployment that creates materialized views using links.  The temporary creation of a public database link with embedded credentials is also a security risk and is in fact a problem we are trying to solve.