Oracle Access Control Lists (ACLs)

ACLs control fine-grained network access permissions in Oracle, managing which users/roles can connect to or resolve resources. Stored as XML in Oracle XML DB repository (/sys/acl/).

Key Concepts

View Existing ACLs & Privileges

-- View Network ACLs
SET PAGESIZE 50 LINESIZE 150 COLSEP ' | ' FEEDBACK ON
COLUMN ACL_OWNER FORMAT A15
COLUMN ACL FORMAT A50
COLUMN HOST FORMAT A30
COLUMN LOWER_PORT FORMAT 9999
COLUMN UPPER_PORT FORMAT 9999
SELECT ACL_OWNER, ACL, HOST, LOWER_PORT, UPPER_PORT FROM DBA_NETWORK_ACLS;

-- View ACL Privileges
COLUMN PRINCIPAL FORMAT A20
COLUMN PRIVILEGE FORMAT A15
SELECT ACL_OWNER, ACL, PRINCIPAL, PRIVILEGE FROM DBA_NETWORK_ACL_PRIVILEGES;

Create and Manage ACLs

-- 1. Create ACL
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'test.xml',
    description => 'Test ACL for www access',
    principal => 'SCOTT',
    is_grant => TRUE,
    privilege => 'connect');
END;
/
COMMIT;

-- 2. Assign ACL to Host with Port Range
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'test.xml',
    host => 'www.oracle.com',
    lower_port => 80,
    upper_port => 1000);
END;
/
COMMIT;

-- 3. Add Privilege for Another User
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'test.xml',
    principal => 'HR',
    is_grant => TRUE,
    privilege => 'resolve');
END;
/
COMMIT;

Check Permissions for User

SET PAGESIZE 50 LINESIZE 150 COLSEP ' | ' FEEDBACK ON
COLUMN HOST FORMAT A30
COLUMN LOWER_PORT FORMAT 9999
COLUMN UPPER_PORT FORMAT 9999
COLUMN ACL FORMAT A50
COLUMN PRIVILEGE FORMAT A10

SELECT host, lower_port, upper_port, acl,
  DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SCOTT', 'connect'),
         1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls;

Note: Replace 'SCOTT' with your username.

Remove ACLs and Privileges

-- Unassign ACL from Host
BEGIN
  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.oracle.com');
END;
/
COMMIT;

-- Remove Privilege from User
BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
    acl => 'test.xml',
    principal => 'SCOTT',
    privilege => 'connect');
END;
/
COMMIT;

-- Drop ACL
BEGIN
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'test.xml');
END;
/
COMMIT;

Troubleshooting ORA-24247 (Network Access Denied)

• Verify ACL assigned for host and port using:

SELECT ACL, HOST, LOWER_PORT, UPPER_PORT FROM DBA_NETWORK_ACLS;

• Ensure user has proper privileges (ADD_PRIVILEGE).
• Confirm ACL is assigned to the correct host/IP.

Common Use Cases

-- Grant access to all hosts and ports
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'test.xml',
    host => '*',
    lower_port => 1,
    upper_port => 9999
  );
END;
/
COMMIT;

-- Remove specific user privilege
BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
    acl => 'test.xml',
    principal => 'HR',
    privilege => 'resolve'
  );
END;
/
COMMIT;

Summary

Oracle ACLs allow secure, fine-grained control over network resource access. Use the DBMS_NETWORK_ACL_ADMIN package to create, assign, modify, and remove ACLs and privileges. Regular ACL reviews help maintain security and operational control.