We have 2 databases (db-1 and db-2), and 2 users (user-1 and user-2) setup on both of the databases. Our databases use only kerberized connections. From db-1, there is a database link created under user-1 (db-link) that points to db-2. There is a procedure on db-1 under user-1 that accesses a table in db-2: (pseudo code below)
create procedure load_data_across_db_link
insert into user-1.table(cols) select cols from user-1.table@db-link;
When the Java application connects to db-1 using user-1 credential cache (since the DB is kerberized) and executes the above procedure, it works and data is getting populated into table. But when the Java application connects using user-2 credential cache, the procedure gives the error:
"ORA-04052 error occurred when looking up remote object"
We believe this could be due to the fact that when the procedure is executed by user-2, it doesn't have access to the credentials cache of user-1 to access the object over the remote link. So we created another db link with the same name (db-link) under user-2. But even then we get the same error
How can we setup the accounts such that Java application connects using user-2 and executes the procedure owned by user-1 to load the data across the DB link ? (Please note that we have provided rights for user-2 to perform DML on user-1 owned objects, and execute permission on the above mentioned procedure)
Edit: When we try to connect to user-2 using "proxy" access in SQL Developer, and run the package, we get "ORA-12662: proxy ticket retrieval failed" (our DBA said that we won't be able to use proxy access to test database links, so we have always deployed the code and then run it using "user-1" (or user-2) account
Thanks for your patience.
I passed this onto Russ Lowenthal from the security team. He advised:
"You need to ensure the Kerberos tickets are forwardable. This is done with a parameter in the LIBDEFAULTS section of the krb5.conf file"
If this does not work, please raise an SR so we can dig a little deeper.