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.