ORA-24248 XMLDB extensible security not installed

ORA-24248 XMLDB extensible security not installed

Oracle 11g introduces more secure and fine grained access on network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR, i.e. an execute privilege on these packages is not enough to access an external network resource using these packages. You have to configure ACL (Access Control List), assign the network host and port to it and grant connect privilege to the users through this ACL .

Please see: Oracle 11g Access Control List for External Network Services

These ACLs are XML files which are stored in the XML Database repository inside the database itself, which means that XML Database must be installed in the database to use the network PL/SQL packages. XML Database is installed automatically when you create a database using DBCA with default options. However, if you unselect/uncheck the XML Database option in DBCA while creating the database, XML Database won't installed.

A few days ago I ran into this problem. We upgraded one of our development database from oracle 10gR2 to 11gR1 recently. After the upgrade the application was broken very badly. Any user trying to login to the application was getting the error below:

ORA-24248: XMLDB 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

After I investigated, I found that that in one of Java classes in the application, where a connection was being established to the databases, a query was being executed like this.

SELECT global_name,utl_inaddr.get_host_address FROM global_name;

When I saw UTL_INADDR being called, I realized instantly that we need an ACL for this call and ACL needs XML Database. The error above is quite self explanatory. I went ahead and verified the existence of Oracle XML Database and found the following:

SQL> select comp_name from dba_registry;

COMP_NAME
----------------------------------------
OWB
Oracle Application Express
Oracle Enterprise Manager
OLAP Catalog
Spatial
Oracle Multimedia
Oracle Text
Oracle Expression Filter
Oracle Rules Manager
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API

17 rows selected.

As can be seen from the output of the above query, we were missing Oracle XML Database. I was able to reproduce the problem in sqlplus.

SQL> conn scott/tiger
Connected.

SQL> select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
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

SQL>

Install XML Database: Oracle 11g Enterprise Edition

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool install_xml_db.log
SQL> @catqm xdb sysaux temp YES
-- xdb is the password for XML Database repository schema
-- sysaux is the default tablespace for XML Database repository schema.
-- temp is the default temporary tablespace for XML Database repository schema.
-- YES is for using secure files for xdb$resource
.
.
.
[output trimmed]
.
.
.
SQL> declare
  2    suf  varchar2(26);
  3    stmt varchar2(2000);
  4  begin
  5    select toksuf into suf from xdb.xdb$ttset where flags = 0;
  6    stmt := 'grant all on XDB.X$PT' || suf || ' to DBA';
  7    execute immediate stmt;
  8    stmt := 'grant all on XDB.X$PT' || suf || ' to SYSTEM WITH GRANT OPTION';
  9    execute immediate stmt;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>spool off

The XML Database installation is complete. I opened the install_xml_db.log and didn't find any significant errors in it. Then I verified from dba_registry again to see the status of XML Database installed.

SQL> select comp_name , status from dba_registry;

COMP_NAME                                STATUS
---------------------------------------- --------------------------------------------
Oracle XML Database                      VALID
OWB                                      VALID
Oracle Application Express               VALID
Oracle Enterprise Manager                VALID
OLAP Catalog                             VALID
Spatial                                  VALID
Oracle Multimedia                        VALID
Oracle Text                              VALID
Oracle Expression Filter                 VALID
Oracle Rules Manager                     VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             VALID
Oracle XDK                               VALID
Oracle Database Java Packages            VALID
OLAP Analytic Workspace                  VALID
Oracle OLAP API                          VALID

18 rows selected.

SQL>

The status should be VALID to indicate that the installation was successfull. Then I tested the UTL_INADDR again.

SQL> conn scott/tiger
Connected.
SQL> select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

As you see now the ORA-24248 is gone because we have XML Database installed. Now we are seeing ORA-24247, which means we can't access any network host using UTL_INADDR unless we allow access to this host via ACL.

SQL> conn / as sysdba
Connected.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'my_application_acl.xml',
        description  => 'ACL for users of my application.',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'resolve',
        start_date   => null,
        end_date     => null
	);
END;
/

PL/SQL procedure successfully completed.

/*
 All that UTL_INADDR.get_host_address does is resolve the host and return the IP Address.
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL creates an ACL and grant privilege to a user or role 
 to that ACL.
 For the sake of this article I am using SCOTT as the grantee of the privilege resolve but 
 in my application I granted this privilege to a role which was assigned to all the 
 application users. So through that role all the users were granted resole privilege to 
 this ACL.
*/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
     acl         =>'my_application_acl.xml',
     host        => 'localhost',
     lower_port  => null,
     upper_port  => null
     );
END;
/

PL/SQL procedure successfully completed.

/*
 When UTL_INADDR.get_host_address is invoked without a host name specified with it,
 it sends the request to localhost. So we need to assign our ACL to the host "localhost".
*/

commit;

SQL> conn scott/tiger
Connected.
SQL> set lines 10000
SQL> column global_name format a20
SQL> column get_host_address format a15
SQL> select global_name,utl_inaddr.get_host_address from global_name;

GLOBAL_NAME          GET_HOST_ADDRES
-------------------- ---------------
ORA11G.HOME.COM      127.0.0.1

SQL>

As you can see above, SCOTT is now able to get server's IP Adress using utl_inaddr.get_host_address. And this solves our problem with the application.

Uninstall XML Database: Oracle 11g Enterprise Edition

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @catnoqm.sql
-- This will uninstall the Oracle XML Database.

See also:
Oracle 11g Access Control List for External Network Services

From http://oracleflash.com/40/ORA-24248-XMLDB-extensible-security-not-installed.html

and Thanks Zahid Karim