A chain of synonyms #JoelKallmanDay

The use of synonyms.

If you have felt a cold sweat, you probably experienced something similar.

Many users want to access application tables “transparently.” Note that I put it in quotes because calling “transparent” “without prefixing the owner user” seems more appropriate to me.

Yes, in production the user is called PEPE, and in development he is called JUAN, and the code must work whatever the user’s name is, but when we start to “universalize” access to objects, the following happens to us .

CASE 1:

• JUAN has the TEST table. Create a public synonym that everyone has access to.
• JUAN deletes the TEST table. Because it’s his and he wants it. But he does not erase the synonym.
• JUAN tries to access the TEST table that he just deleted, and the error that appears is this:

SQL> connect juan/juan
Conected.

SQL> create table test (id number);
Table created.
SQL> create public synonym test for juan.test;
Synonym created.

SQL> drop table test;
Table dropped.

SQL> desc test
SP2-0749: No se puede resolver la ruta de acceso circular del sinonimo "test"

SQL> select * from test;
select * from test
*
ERROR in line 1:
ORA-01775: looping chain of synonyms

CASE 2:

• JUAN wants to access the TEST table of the user PEPE.
• JUAN creates a private synonym to view PEPE.TEST “transparently”.
• MARIA wants to access JUAN.TEST, without knowing that it is not actually a table, but rather a synonym for another user, also transparently.
• MARIA creates a public synonym to access JUAN.TEST.

SQL> connect pepe/pepe
Conected.
SQL> create table test (id number);

Table created.

SQL> connect juan/juan
Conected.
SQL> create synonym test for pepe.test;

Synonym created.

SQL> connect maria/maria
Conected.
SQL> create public synonym test for juan.test;

Synonym created.

Everyone sees the TEST table.

SQL> connect maria/maria
Conected.

SQL> desc test
Nombre ┐Nulo? Tipo
----------------------------------------------------- -------- ------------------------------------
ID NUMBER

SQL> connect juan/juan
Conected.
SQL> desc test
Nombre ┐Nulo? Tipo
----------------------------------------------------- -------- ------------------------------------
ID NUMBER

SQL> connect pepe/pepe
Conected.
SQL> desc test
Nombre ┐Nulo? Tipo
----------------------------------------------------- -------- ------------------------------------
ID NUMBER

• PEPE deletes his table, and when he tries to access TEST it resolves the public synonym of MARIA, which points to the private synonym of JUAN, which points to the table that PEPE just deleted.

SQL> select count(*) from test;
select count(*) from test
*
ERROR in line 1:
ORA-01775: looping chain of synonyms

And to solve these problems, here I leave you a home made procedure recipe, so you can identify the problematic synonyms. You have the project on the Café Database GitHub and, as always, since it’s open source and all, feel free to modify and extend as you consider.

https://github.com/CafeDatabase/Check-Synonyms

-- Ensure user has DBA privileges.
create or replace procedure CHECK_SYNONYMS (p_owner varchar2) 
is
  usuario varchar2(30);
  bucle_sinonimos exception;
	sinonimo_no_valido exception;
	PRAGMA EXCEPTION_INIT(bucle_sinonimos,-1775);
	PRAGMA EXCEPTION_INIT(sinonimo_no_valido,-980);
	filas number;
begin 
  dbms_output.enable;
  for x in (select owner, synonym_name,table_owner,table_name,NVL2(db_link,'@'||db_link,null) db_link 
              from dba_synonyms where owner IN (p_owner,'PUBLIC'))
			  -- It takes time to check SYS synonyms, but I think it's worth it.
	loop
	   begin 
		     select DECODE(x.owner,'PUBLIC','',x.owner||'.') into usuario from dual;
			 execute immediate 'select count(*) into :filas from '||usuario||x.synonym_name||' where 1=0' into filas;
	   exception 
		   when bucle_sinonimos then 
			   dbms_output.put_line('Synonym in a loop chain '||x.owner||'.'||x.synonym_name||' pointing to '
				            ||x.table_owner||'.'||x.table_name||x.db_link);
									
		   when sinonimo_no_valido then 
			   dbms_output.put_line('Synonym not valid '||x.owner||'.'||x.synonym_name||'  pointing to '
				            ||x.table_owner||'.'||x.table_name||x.db_link);
			 when others then null;
	   end;
	end loop;
end;
/