How to Get List of ACLs in Oracle 11g and Migrate Them to Another Database (Complete Guide)

When working with Oracle 11g, especially during database migrations, one often-overlooked component is Network Access Control Lists (ACLs). If you’ve ever encountered errors like:

ORA-24247: network access denied by access control list (ACL)

…then you already know how critical ACLs are.

In this guide, we’ll walk through how to extract ACLs from a source database and migrate them safely to a target database, with practical steps, real DBA insights, and common pitfalls to avoid.

---

What Are ACLs in Oracle 11g?

In Oracle 11g, ACLs control which users can access external network services using packages like:

• UTL_HTTP
• UTL_SMTP
• UTL_TCP
• UTL_INADDR

These ACLs are stored as XML files inside the Oracle XML DB (XDB repository), not as files on the operating system.

For example:

/sys/acls/utl_http.xml

👉 This is a logical path, not a physical file.

---

Why ACL Migration Is Important

When you migrate a database using Data Pump (expdp/impdp):

• Tables, indexes, and schemas are migrated ✅
• ACLs are NOT reliably migrated ❌

This leads to:

• Broken integrations
• Email failures
• HTTP call failures
• ORA-24247 errors

👉 So ACL migration must be handled manually

---

Step 1: Get List of ACLs from Source Database

To begin, extract all ACLs:

SELECT acl,
       host,
       lower_port,
       upper_port
FROM dba_network_acls;

This shows:

• ACL file name
• Associated host
• Port range

---

Step 2: Get ACL Privileges

Next, identify who has access:

SELECT acl,
       principal,
       privilege,
       is_grant
FROM dba_network_acl_privileges;

👉 This tells you:

• Which user (principal)
• What privilege (connect, resolve)

---

Step 3: Generate ACL Creation Scripts

Oracle doesn’t provide a direct export for ACLs, so you need to generate scripts manually.

Create ACL Script

SELECT 
'BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(' ||
' acl => ''' || acl || ''',' ||
' description => ''Migrated ACL'',' ||
' principal => ''' || principal || ''',' ||
' is_grant => TRUE,' ||
' privilege => ''' || privilege || '''); END; /'
FROM dba_network_acl_privileges;

⚠️ Important:

• Only run CREATE_ACL once per ACL
• Do NOT duplicate for multiple privileges

---

Step 4: Generate ADD_PRIVILEGE Scripts

If an ACL has multiple privileges:

SELECT 
'BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(' ||
' acl => ''' || acl || ''',' ||
' principal => ''' || principal || ''',' ||
' is_grant => TRUE,' ||
' privilege => ''' || privilege || '''); END; /'
FROM dba_network_acl_privileges;

---

Step 5: Generate ASSIGN_ACL Scripts

SELECT 
'BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(' ||
' acl => ''' || acl || ''',' ||
' host => ''' || host || ''',' ||
' lower_port => ' || 
CASE WHEN lower_port IS NULL THEN 'NULL' ELSE TO_CHAR(lower_port) END || ',' ||
' upper_port => ' || 
CASE WHEN upper_port IS NULL THEN 'NULL' ELSE TO_CHAR(upper_port) END ||
'); END; /'
FROM dba_network_acls;

---

Step 6: Apply in Target Database

Follow this correct order:

Create ACL (only if not exists)

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => '/sys/acls/custom_acl.xml',
    description => 'Migrated ACL',
    principal   => 'IILPRS',
    is_grant    => TRUE,
    privilege   => 'connect'
  );
END;
/

---

2️⃣ Add Additional Privileges

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl       => '/sys/acls/custom_acl.xml',
    principal => 'IILPRS',
    is_grant  => TRUE,
    privilege => 'resolve'
  );
END;
/

---

3️⃣ Assign ACL to Host

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl        => '/sys/acls/custom_acl.xml',
    host       => '198.101.105.220',
    lower_port => 8080,
    upper_port => 8080
  );
END;
/

---

4️⃣ Commit Changes

COMMIT;

---

Common Mistakes to Avoid

❌ Creating ACL Twice

ORA-31003: already exists

👉 Fix: Use ADD_PRIVILEGE instead

---

❌ Using Wrong Parameters

ADD_PRIVILEGE does NOT support description

---

❌ Wrong Port Mapping

If your app uses:

http://host:8080

But ACL uses:

105–220

👉 It will fail

---

❌ Assuming PUBLIC Works Always

👉 Always grant to actual runtime user

---

Step 7: Validate ACL Migration

Check ACLs

SELECT * FROM dba_network_acls;

Check Privileges

SELECT * FROM dba_network_acl_privileges;

---

Real DBA Tip (Very Important)

If you’re unsure what’s missing, temporarily allow all:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl  => '/sys/acls/utl_http.xml',
    host => '*'
  );
END;
/

👉 If it works → issue is ACL configuration

---

Understanding ACL Storage

ACLs are stored internally in:

• XML DB repository
• Tables like XDB$ACL

👉 Not in OS filesystem

---

📌 Final Thoughts

Migrating ACLs in Oracle 11g is not automatic, but once you understand the structure, it becomes straightforward.

Key Takeaways:

• ACLs control external network access
• Stored inside Oracle, not OS
• Must be migrated manually
• Always match:
  • User
  • Host
  • Port
  • Privileges