3 Replies Latest reply: Sep 22, 2010 11:02 PM by sathya - oracle RSS

    11g Upgrade - Network ACL

    shakilshaikh
      I want to upgrade my oracle 10g database to 11g. the utlui112.sql script shows following -

      WARNING: --> Database contains schemas with objects dependent on network packages.
      .... Refer to the Upgrade Guide for instructions to configure Network ACLs.
      .... USER MDMSYS has dependent objects.

      According to documentation , it is not clear whether I need to install XML DB before upgrade or after upgrade to 11g.

      I run the following query and result is as follows -

      SQL >SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
      AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

      OWNER NAME TYPE REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
      ------------------------------ ------------------------------ ----------------- ------------------------------ ---------------------------------------------------------------- -----------------
      REFERENCED_LINK_NAME DEPE
      -------------------------------------------------------------------------------------------------------------------------------- ----
      MDMSYS MDM_JOB PACKAGE BODY PUBLIC UTL_TCP SYNONYM
      HARD

      MDMSYS MDM_JOB PACKAGE BODY MDMSYS UTL_TCP NON-EXISTENT

      Can someone plaease help on how I can configure the network ACLs?
        • 2. Re: 11g Upgrade - Network ACL
          Tubby
          shakilshaikh wrote:
          I want to upgrade my oracle 10g database to 11g. the utlui112.sql script shows following -

          Can someone plaease help on how I can configure the network ACLs?
          The first link when i googled "oracle 11 configure network acl"
          http://www.pythian.com/news/3434/setting-up-network-acls-in-oracle-11g-for-dummies/

          Searching isn't that complex and actually works a great deal of the time (unless you don't attempt it, then it has a pretty high failure rate).
          • 3. Re: 11g Upgrade - Network ACL
            sathya - oracle
            Hi ,

            You can grant to a network and not necessary to grant each machines IP details.
            Also this has to be granted to users or the principal is the schema who will be executing this utl_smtp.
            If there are multiple users, then you need to grant access to each user.

            You need to configure below steps to grant access to the user for utl operations.
            This is a new security feature to 11g.

            Please review below document :
            Oracle® Database Security Guide
            11g Release 1 (11.1)
            Part Number B28531-06
            4 Configuring Privilege and Role Authorization
            Managing Fine-Grained Access to External Network Services
            URL : http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm#CIHDAJDJ


            A example of the setting:
            =================
            If you are creating the ACL for the first time, you can directly go to step (d).
            Please replace the values in < > with your environment values.

            a. Drop the user privilege:(please run the below for all the users who are granted connect privilege).
            BEGIN
            DBMS_NETWORK_ACL_ADMIN.delete_privilege (
            acl => '<mailserver_acl.xml>',
            principal => '<MYUSER>',
            is_grant => FALSE,
            privilege => 'connect');

            COMMIT;
            END;
            /

            b. Unassign the network details from ACL (The ip address are only example, please replace with the
            values you have specified)
            BEGIN
            DBMS_NETWORK_ACL_ADMIN.unassign_acl (
            acl => '<mailserver_acl.xml>',
            host => '<192.168.2.3>',
            lower_port => <25>,
            upper_port => <25>);
            COMMIT;
            END;
            /

            c. Drop the ACL

            BEGIN
            DBMS_NETWORK_ACL_ADMIN.drop_acl (
            acl => '<mailserver_acl.xml>');

            COMMIT;
            END;
            /

            d. Create the acl again fresh:

            BEGIN
            DBMS_NETWORK_ACL_ADMIN.create_acl (
            acl => 'mailserver_acl.xml',
            description => 'Mailserver ACL',
            principal => '<MYUSER>',
            is_grant => TRUE,
            privilege => 'connect',
            start_date => SYSTIMESTAMP,
            end_date => NULL);

            COMMIT;
            END;
            /

            e. Assign the acl to the network:(please have the ip address modified to correct IP of the machine where this utl package is targetted run.)
            for example IP/hostname of mail server should be there for UTL_SMTP to execute.


            BEGIN
            DBMS_NETWORK_ACL_ADMIN.assign_acl (
            acl => 'mailserver_acl.xml',
            host => '<192.168.2.3>',
            lower_port => <25>,
            upper_port => <25>);

            COMMIT;
            END;
            /

            f.Test the package.


            Thanks,
            Sathya