how to run UTL_TCP, UTL_SMTP and the like in 11g

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed

I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco :
The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged users. This default behavior is different from, and is incompatible with, previous versions of Oracle Database.

I do not want to install XDB to send mail. Sounds like an overkill…

Ok, as an hard core dba I created a wrapper in the sys schema, something you probably should not do !

ex:
10g

SQL> conn scott/tiger
Connected.
SQL> select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual;
UTL_INADDR.GET_HOST_ADDRESS('LOCALHOST')
--------------------------------------------------
127.0.0.1

after upgrade
11g

SQL> conn scott/tiger
Connected.
SQL> select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual;
select utl_inaddr.GET_HOST_ADDRESS('localhost') from dual
*
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

My workaround to “disable” Fine-Grained Access to External Network Services

SQL> conn / as sysdba
SQL> create or replace function my_utl_inaddr_GET_HOST_ADDRESS(HOST VARCHAR2) return VARCHAR2 is begin return utl_inaddr.GET_HOST_ADDRESS; end;
2 /

Function created.

SQL> grant execute on my_utl_inaddr_GET_HOST_ADDRESS to scott;

Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> select sys.my_utl_inaddr_GET_HOST_ADDRESS('localhost') from dual;
SYS.MY_UTL_INADDR_GET_HOST_ADDRESS('LOCALHOST')
--------------------------------------------------
127.0.0.1

If you want to use the recommended way of granting access to utl_tcp and the like, check note 453756.1

6 thoughts on “how to run UTL_TCP, UTL_SMTP and the like in 11g”

  1. Sokrates

    no sys-hacking needed:

    create or replace and compile java source named inet as
    public class Inaddr
    {
    public static String getByName(String host) throws java.net.UnknownHostException
    {
    return java.net.InetAddress.getByName(host).getHostAddress();
    }

    }
    /

    create or replace function my_utl_inaddr_GET_HOST_ADDRESS(host varchar2) return varchar2 is
    LANGUAGE JAVA NAME ‘Inaddr.getByName(java.lang.String) return java.lang.String’;
    /

    sql > select my_utl_inaddr_GET_HOST_ADDRESS(‘localhost’) from dual;

    MY_UTL_INADDR_GET_HOST_ADDRESS(‘LOCALHOST’)
    ———————————————————————-
    127.0.0.1

  3. Igor

    @Sokrates

    There is some “hacking” still needed by SYS (permission grant).

    ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.net.SocketPermission …

    has not been granted to MXY. The PL/SQL to grant this is
    dbms_java.grant_permission( ‘MXY’, ‘SYS:java.net.SocketPermission’, ‘porec.fr.murex.com’, ‘resolve’ )

    Regards

  4. Laurent Schneider Post author

    even in the note 453756.1 I mentioned, the specified grant to the ‘*’ host. So what’s the point? Enforce to use fine grained network access control and recomment to give access to everyhing? Overprotective and annoying approach. That said, install XDB and grant * is the things you will do if you are upgrading hundreds of database and do expect a “10g” compatibility

Comments are closed.