Oracle PL/SQL EXTPROC Security Alert Question

From: Pat Howe <phowe_at_Illuminet.com>
Date: Fri, 15 Feb 2002 11:26:28 -0800
Message-ID: <F001.00411623.20020215112424@fatcity.com>

Oracle has issued the following PL/SQL EXTPROC security alert : http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NEW&p_id=140815.995

I want to determine if the applications I am supporting are using External Procedures.
If they are not then I will utilize Oracle's recommended solution of removing EXTPROC from the server's LISTENER.ORA and TNSNAMES.ORA files. If they are then I will need to implement the 2nd solution (as talked about in the above link).

So how do I determine if External Procedures are being used by the Canned apps ?
Currently I am taking the approach : If External Procedures are not configured (properly) on the Oracle server then for sure it I am not using it.
If you know of a better approach I am open to suggestions.

Therefore I looked up how to configure SQL*Net for External Procedures. The procedure required the following changes :

Add a system identifier (SID) name and a program name for EXTPROC in the

server's LISTENER.ORA file:

LISTENER =
(ADDRESS_LIST =
  (ADDRESS =
(PROTOCOL = IPC)
(KEY = oracle.world)

  )
  (ADDRESS =
(PROTOCOL = IPC)
(KEY = ORCL)

  )

  (ADDRESS =                        <-- add these lines 


   (PROTOCOL = IPC)                 <-- add these lines 

   (KEY = extproc)                  <-- add these lines 

  )                                 <-- add these lines

  (ADDRESS =
(PROTOCOL = TCP)
(Host = <hostname>)
(PORT = 1521)

  )
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = ADMIN
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
(SID_NAME = ORCL)

  )

  (SID_DESC =                      <-- add these lines 


   (SID_NAME = extproc)            <-- add these lines 

   (PROGRAM = extproc)             <-- add these lines   

  )                                <-- add these lines

)

2. Add a service name entry for EXTPROC in the server's TNSNAMES.ORA file:

orcl.world =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(Host = <hostname>)
(PORT = 1521)

)
(CONNECT_DATA = (SID = ORCL))
)

extproc_connection_data.world =                 <-- add these lines 
 (DESCRIPTION =                                 <-- add these lines 
  (ADDRESS =                                    <-- add these lines 


   (PROTOCOL = IPC)                             <-- add these lines 

   (KEY = ORCL)                                 <-- add these lines 

  )                                             <-- add these lines 
 (CONNECT_DATA = (SID=extproc))) <-- add these lines

===

Here is my problem and question :

Two of my servers appear to have EXTPROC half way configured :

Server One
The TNSNAMES.ORA file does not have the EXTPROC configured. The LISTENER.ORA has the LISTENER EXTPROC configured but not the SID_LIST_LISTENER EXTPROC configured.

Server Two
The TNSNAMES.ORA file does not have the EXTPROC configured. The LISTENER.ORA has both the LISTENER EXTPROC and the SID_LIST_LISTENER EXTPROC configured.

Because the configurations appear to be half cooked - can I assume that PL/SQL EXTPROC is not working on these servers - thus I can proceed with removing these references from the LISTENER and TNSNAMES ?

Thanks in advance.

Patrick J. Howe
Oracle DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pat Howe
  INET: phowe_at_Illuminet.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Feb 15 2002 - 13:26:28 CST