CODEWITHSUNDEEP
oracle-databaseprivilegessynonym
Roman
Roman

Reputation: 567

Recompiling a synonym in another schema of Oracle DB

I want to recompile a broken synonym in another schema but get an error about privileges.

As per Oracle states:

To modify a private synonym in another user's schema, you must have the CREATE ANY SYNONYM and DROP ANY SYNONYM system privileges.

Okay, see my snippet:

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 24 18:47:29 2020

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select user, sys_context( 'userenv', 'current_schema' ) cur_schema from dual;

USER
------------------------------
CUR_SCHEMA
--------------------------------------------------------------------------------
STAT_ADM
STAT_ADM


SQL> SELECT s.privilege
  2    FROM dba_sys_privs s
  3   WHERE s.grantee = USER
  4     AND s.privilege LIKE '%ANY%SYNONYM%';

PRIVILEGE
----------------------------------------
DROP ANY SYNONYM
CREATE ANY SYNONYM

SQL> alter synonym ADB011_T_PRO.SA_BRAND compile;
alter synonym ADB011_T_PRO.SA_BRAND compile
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select dbms_metadata.get_ddl(object_type => 'SYNONYM'
  2                              ,NAME        => 'SA_BRAND'
  3                              ,SCHEMA      => 'ADB011_T_PRO') ddl_code from dual;

DDL_CODE
--------------------------------------------------------------------------------

  CREATE OR REPLACE SYNONYM "ADB011_T_PRO"."SA_BRAND" FOR "STAT_INT"."SA_BRAND"


SQL>

Do I really lack of some permission? Or how should I properly recompile synonym? I have also CREATE ANY SYNONYM permission so I workaround it by issuing DDL statement of this synonym to make it valid again but I want to use a compile option.

Upvotes: 1

Views: 4474

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6084

This does seem to be documented as Oracle Bug 4189542 (Doc ID 4189542.8). From your code, it looks like you are using a version 11.2.0.4 database. If you update your version of Oracle, or apply a recent patch set to your 11.2.0.4 database, then it should resolve the issue.

Upvotes: 2

Related Questions