2

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.

2 Answers 2

1

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.

0

Proxy user. Use the feature. And forget SYS.

create user app1 identified by app1;
create user app2 identified by app2;
create user app3 identified by app3;

create user deploy_user identified by deploy_user;

grant create session to app1, app2, app3, deploy_user;

alter user app1 grant connect through deploy_user;
alter user app2 grant connect through deploy_user;
alter user app3 grant connect through deploy_user;

connect deploy_user[app1]/deploy_user
SQL> show user
USER is "APP1"
.. run scripts ..
connect deploy_user[app2]/deploy_user
SQL> show user
USER is "APP2"
.. run scripts ..
connect deploy_user[app3]/deploy_user
SQL> show user
USER is "APP3"
.. run scripts ..
1
  • I should have included that in the list of things I tried. It gives the exact same error. It seems to still be passing the logged in users password to authenticate with the remote database even though show user indicates the correct user. Proxy users are indeed great, unfortunately they don't help me here. Commented May 9, 2018 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.