Pete Finnigan's Oracle Security Weblog

I got asked a question via a DM on one of my social media channels a few days ago and thought it worth an investigation. They asked me; Can an Oracle synonym point to another synonym, ad infinitum.

Let me do a test and see. First what privileges exist that involve synonyms:

SQL> select name from system_privilege_map where name like '%SYNONYM%';

NAME
----------------------------------------
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
DROP ANY SYNONYM
CREATE ANY SYNONYM
CREATE SYNONYM

SQL>

There are two groups of synonym privileges; drop and create PUBLIC synonyms and drop and create ANY and one single privilege CREATE SYNONYM to create a single synonym in a schema. For the single create of synonym in your own schema you do not need a DROP because of the OBJECT OWNER PRINCIPAL as the owner of an object once its created does not need privileges to change their own objects.

Let me create a user and then add permissions and connect to the user:

SQL> create user syntest identified by syntest;

User created.

SQL> grant create session, create synonym to syntest;

Grant succeeded.

SQL> connect syntest/syntest@//192.168.56.33:1539/xepdb1
Connected.
SQL>

Now as this user create a synonym:

SQL> create synonym all_users for sys.all_users;

Synonym created.

SQL>

Now try and create another synonym pointing to this synonym and just for fun create another synonym to that synonym so that we have synonym to synonym to sys.all_users

SQL> create synonym all_users1 for all_users;

Synonym created.

SQL> create synonym all_users2 for all_users1;

Synonym created.

SQL>

That works. What does the meta data show:

SQL> set serveroutput on
SQL> @sc_print 'select * from dba_synonyms where synonym_name like ''''ALL_USERS%'''''
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from dba_synonyms where synonym_name like ''ALL_USERS%''','''','''''');
Executing Query [select * from dba_synonyms where synonym_name like
'ALL_USERS%']
OWNER                         : PUBLIC
SYNONYM_NAME                  : ALL_USERS
TABLE_OWNER                   : SYS
TABLE_NAME                    : ALL_USERS
DB_LINK                       :
ORIGIN_CON_ID                 : 1
-------------------------------------------
OWNER                         : SYNTEST
SYNONYM_NAME                  : ALL_USERS
TABLE_OWNER                   : SYS
TABLE_NAME                    : ALL_USERS
DB_LINK                       :
ORIGIN_CON_ID                 : 3
-------------------------------------------
OWNER                         : SYNTEST
SYNONYM_NAME                  : ALL_USERS1
TABLE_OWNER                   : SYNTEST
TABLE_NAME                    : ALL_USERS
DB_LINK                       :
ORIGIN_CON_ID                 : 3
-------------------------------------------
OWNER                         : SYNTEST
SYNONYM_NAME                  : ALL_USERS2
TABLE_OWNER                   : SYNTEST
TABLE_NAME                    : ALL_USERS1
DB_LINK                       :
ORIGIN_CON_ID                 : 3
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, yes synonyms can point at synonyms ad-infinitum. We can also use these synonyms and they work:

SQL> connect syntest/syntest@//192.168.56.33:1539/xepdb1
Connected.
SQL> select count(*) from all_users2;

  COUNT(*)
----------
        87

SQL> select count(*) from all_users1;

  COUNT(*)
----------
        87

SQL> select count(*) from all_users;

  COUNT(*)
----------
        87

SQL> select count(*) from sys.all_users;

  COUNT(*)
----------
        87

SQL>

Hmm, this is a confusing situation to be in if you have a chain of synonyms pointing eventually to an object. From a security perspective this would be hard to understand and to ensure everything was correct.

#oracleace #oracleacepro #sym_42 #oracle #database #security #synonyms