Grant command on a synonym throws ORA-01775 error

Question

I am trying a create a synonym on a trigger and then tries to give grant all on the synonym for a specific user. The it throws an error : ORA-01775: looping chain of synonyms

Below is my code. Could anybody point me the right direction where I am wrong!

create table emp(emp_name varchar(20));
create or replace trigger trg_test
before insert on emp
begin
null;
end;

create or replace public synonym  trg_test for trg_test;
GRANT ALL ON trg_test TO USER1;

Answer 1

You are getting the error in case you have performed the following tasks:

CREATE OR REPLACE public SYNONYM TRG_TEST FOR TRG_TEST;
GRANT ALL ON TRG_TEST TO USER1; --this will give error

Error:
GRANT ALL ON TRG_TEST TO USER1
Error report -
ORA-01775: looping chain of synonyms
01775. 00000 -  "looping chain of synonyms"
*Cause:    
*Action:

If as suggested by @Frank Schmitt, you put the schema name before trigger name in synonym then also you will get the same error:

CREATE OR REPLACE public SYNONYM TRG_TEST FOR schema.TRG_TEST;
GRANT ALL ON TRG_TEST TO USER1; --this will give error

Error:
GRANT ALL ON TRG_TEST TO USER1
Error report -
ORA-01775: looping chain of synonyms
01775. 00000 -  "looping chain of synonyms"
*Cause:    
*Action:

Let me clear this, THIS ERROR MESSAGE IS MISLEADING.

You can create a synonym of almost any objects in oracle regardless of its type (table, view, sequence, etc). Oracle does not know what is the object type behind the synonym.

Now, In our case, if Oracle knows that the GRANT is going to be performed on TRIGGER then it will give a different error.

-- actual error
drop public synonym TRG_TEST;
GRANT ALL ON TRG_TEST TO USER1;

Error starting at line : 16 in command -
GRANT ALL ON TRG_TEST TO USER1
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

So the conclusion is: You cannot give grants for the trigger. There is no such thing. Triggers fire automatically whenever the trigger event is done on the table on which the trigger is created. You only need to grant privilege on the table.

Cheers!!