Infrastructure at your Service

Yann Neuhaus

Getting rid of network acces issues after migrating to Oracle 11.2

Are you experiencing network access issues after a migration to Oracle Database 11g Release 2 (11.2)? If yes, you have to make sure the concerned PL/SQL packages get access to your network again. This posting will show you how.

While migrating to Oracle 11 Release 2, the following PL/SQL packages will not be allowed to get network accesses anymore (security restrictions): UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL. This is why you will have to create network ACLs (Access Control Lists) after the migration.

You can do this with the package dbms_network_acl_admin. However, to compile this package, XDB must be installed on the database. Below is an example how to install XDB:

connect / as SYSDBA
@$ORACLE_HOME/rdbms/admin/catqm.sql xdb SYSAUX TEMP

“xdb” is the password for the XDB user.
“SYSAUX” stands for the tablespace where XDB will be installed.
“TEMP” simply is the temporary tablespace.

Now, you are ready to start:

connect / as SYSDBA
@$ORACLE_HOME/rdbms/admin/catxdbj.sql;
@$ORACLE_HOME/rdbms/admin/catrul.sql;

From this point on, the network ACLs can be configured. Below is an example for a full public network access, allowing all Oracle users (PUBLIC) to resolve and connect to any kind of servers (‘*’):

SQL> begin
begin
 dbms_network_acl_admin.drop_acl(
 acl =>         'all-network-PUBLIC.xml'
 );
 exception
 when others then null;
 end;
 dbms_network_acl_admin.create_acl(
 acl =>         'all-network-PUBLIC.xml',
 description => 'Network connects for all',
 principal =>   'PUBLIC',
 is_grant =>    true,
 privilege =>   'connect'
 );
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
 acl =>         'all-network-PUBLIC.xml',
 principal =>   'PUBLIC',
 is_grant  =>   true,
 privilege =>   'resolve'
 );
 dbms_network_acl_admin.assign_acl(
 acl =>         'all-network-PUBLIC.xml',
 host =>        '*'
 );
end;
/

Have a successful migration to Oracle 11.2!

Best regards,
Yann

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Yann Neuhaus
Yann Neuhaus

Vice-chairman of the Board, Chief Sales Officer (CSO), Region Manager
Region Manager