Exploring Access Control Lists (ACL) Privileges in Oracle

Access Control Lists (ACLs) are crucial for managing fine-grained security in Oracle. They allow administrators to define access permissions for network resources, ensuring users or roles have controlled access to database-related operations.

Key Features of Oracle ACLs

1. Fine-Grained Access Control: ACLs provide security for both table data and Oracle XML DB repository resources.
2. User Types: Supports Oracle Fusion users (non-database users).
3. ACL Storage: ACLs are stored in XML format under /sys/acl/ in the Oracle XML DB Repository.
4. Key Dimensions:
   • Principals: Specify which users or roles are granted access.
   • Privileges: Define allowed operations, e.g., connect, resolve.
   • Objects: Indicate the specific data or resources the permissions apply to.

Viewing Existing ACLs

To inspect the ACLs and associated permissions:

-- Set output formatting
SET PAGESIZE 50;
SET LINESIZE 150;
SET COLSEP ' | ';
SET FEEDBACK ON;

-- Format columns for better readability
COLUMN ACL_OWNER FORMAT A15;
COLUMN ACL FORMAT A50;
COLUMN HOST FORMAT A30;
COLUMN LOWER_PORT FORMAT 9999;
COLUMN UPPER_PORT FORMAT 9999;
COLUMN PRINCIPAL FORMAT A20;
COLUMN PRIVILEGE FORMAT A15;

-- Query to view network ACLs
SELECT ACL_OWNER, ACL, HOST, LOWER_PORT, UPPER_PORT 
FROM DBA_NETWORK_ACLS;

-- Query to view ACL privileges
SELECT ACL_OWNER, ACL, PRINCIPAL, PRIVILEGE 
FROM DBA_NETWORK_ACL_PRIVILEGES;

Output:

ACL_OWNER       | ACL                                                | HOST                           | LOWER_PORT | UPPER_PORT
--------------- | -------------------------------------------------- | ------------------------------ | ---------- | ----------
SYS             | /sys/acls/oracle-sysman-ocm-Resolve-Access.xml     | localhost                      |            |
SYS             | NETWORK_ACL_3D6A1A8749CB43C0AB17CF3BCB6CDE04       | *                              |            |

2 rows selected.

Creating and Managing ACLs

1. Create an 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 a Network Host

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'test.xml',
    host => 'www.oracle.com',
    lower_port => 80,
    upper_port => 1000);
END;
/
COMMIT;

3. Add Privileges for Another User

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'test.xml',
    principal => 'HR',
    is_grant => TRUE,
    privilege => 'resolve');
END;
/
COMMIT;

4. Check Assigned Permissions

-- Set output formatting
SET PAGESIZE 50;
SET LINESIZE 150;
SET COLSEP ' | ';
SET FEEDBACK ON;

-- Format columns for better readability
COLUMN HOST FORMAT A30;
COLUMN LOWER_PORT FORMAT 9999;
COLUMN UPPER_PORT FORMAT 9999;
COLUMN ACL FORMAT A50;
COLUMN PRIVILEGE FORMAT A10;

-- Query to view ACLs with privileges
SELECT host, lower_port, upper_port, acl,
  DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SYS', 'connect'),
         1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls;

Note – Replace SYS with your actual username for which you are checking in above query.

Output:

HOST                           | LOWER_PORT | UPPER_PORT | ACL                                                | PRIVILEGE
------------------------------ | ---------- | ---------- | -------------------------------------------------- | ---------------
localhost                      |            |            | /sys/acls/oracle-sysman-ocm-Resolve-Access.xml     | GRANTED
*                              |            |            | NETWORK_ACL_3D6A1A8749CB43C0AB17CF3BCB6CDE04       | GRANTED

2 rows selected.

5. Unassign ACL from a Host

BEGIN
  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.oracle.com');
END;
/
COMMIT;

6. Remove Privileges for a User

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

7. Drop an ACL

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

Troubleshooting ORA-24247 (Network Access Denied)

If you encounter the error ORA-24247: network access denied by access control list (ACL):

1. Verify the ACL setup for the host and port:

-- Set output formatting
SET PAGESIZE 50;
SET LINESIZE 150;
SET COLSEP ' | ';
SET FEEDBACK ON;

-- Format columns for better readability
COLUMN ACL FORMAT A50;
COLUMN HOST FORMAT A30;
COLUMN LOWER_PORT FORMAT 9999;
COLUMN UPPER_PORT FORMAT 9999;

-- Query to view network ACLs
SELECT ACL, HOST, LOWER_PORT, UPPER_PORT 
FROM DBA_NETWORK_ACLS;

2. Ensure the user has the necessary privileges using DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE.
3. Confirm the ACL is assigned to the required host or IP.

Common ACL Management Scenarios

• Granting all hosts access:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl         => 'test.xml',
    host        => '*',
    lower_port  => 1,
    upper_port  => 9999
  );
END;
/
COMMIT;

• Removing a specific user privilege:

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

Summary

Oracle ACLs offer a robust way to manage network access permissions. Using the DBMS_NETWORK_ACL_ADMIN package, administrators can create, modify, and enforce fine-grained security for database interactions. Regularly monitoring and updating ACLs ensures optimal security compliance and operational efficiency.

For more information, you can refer to the official Oracle documentation on configuring application privileges and Access Control Lists (ACLs) here.