{"id":4172,"date":"2025-06-08T02:30:30","date_gmt":"2025-06-08T02:30:30","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4172"},"modified":"2025-06-09T15:53:33","modified_gmt":"2025-06-09T15:53:33","slug":"oracle-access-control-lists-acls","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-access-control-lists-acls\/","title":{"rendered":"Oracle Access Control Lists (ACLs)"},"content":{"rendered":"\n

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\/<\/code>).<\/p>\n\n\n\n

Key Concepts<\/h3>\n\n\n\n
Term<\/th>Description<\/th><\/tr><\/thead>
Principal<\/strong><\/td>User or role granted permissions<\/td><\/tr>
Privilege<\/strong><\/td>Allowed operations (e.g., connect, resolve)<\/td><\/tr>
Host<\/strong><\/td>Network host or IP address<\/td><\/tr>
Ports<\/strong><\/td>Port range (lower and upper)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

View Existing ACLs & Privileges<\/h3>\n\n\n\n
-- View Network ACLs\nSET PAGESIZE 50 LINESIZE 150 COLSEP ' | ' FEEDBACK ON\nCOLUMN ACL_OWNER FORMAT A15\nCOLUMN ACL FORMAT A50\nCOLUMN HOST FORMAT A30\nCOLUMN LOWER_PORT FORMAT 9999\nCOLUMN UPPER_PORT FORMAT 9999\nSELECT ACL_OWNER, ACL, HOST, LOWER_PORT, UPPER_PORT FROM DBA_NETWORK_ACLS;\n\n-- View ACL Privileges\nCOLUMN PRINCIPAL FORMAT A20\nCOLUMN PRIVILEGE FORMAT A15\nSELECT ACL_OWNER, ACL, PRINCIPAL, PRIVILEGE FROM DBA_NETWORK_ACL_PRIVILEGES;<\/code><\/code><\/pre>\n\n\n\n
Create and Manage ACLs<\/h3>\n\n\n\n
-- 1. Create ACL\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(\n    acl => 'test.xml',\n    description => 'Test ACL for www access',\n    principal => 'SCOTT',\n    is_grant => TRUE,\n    privilege => 'connect');\nEND;\n\/\nCOMMIT;\n\n-- 2. Assign ACL to Host with Port Range\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(\n    acl => 'test.xml',\n    host => 'www.oracle.com',\n    lower_port => 80,\n    upper_port => 1000);\nEND;\n\/\nCOMMIT;\n\n-- 3. Add Privilege for Another User\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(\n    acl => 'test.xml',\n    principal => 'HR',\n    is_grant => TRUE,\n    privilege => 'resolve');\nEND;\n\/\nCOMMIT;<\/code><\/code><\/pre>\n\n\n\n
Check Permissions for User<\/h3>\n\n\n\n
SET PAGESIZE 50 LINESIZE 150 COLSEP ' | ' FEEDBACK ON\nCOLUMN HOST FORMAT A30\nCOLUMN LOWER_PORT FORMAT 9999\nCOLUMN UPPER_PORT FORMAT 9999\nCOLUMN ACL FORMAT A50\nCOLUMN PRIVILEGE FORMAT A10\n\nSELECT host, lower_port, upper_port, acl,\n  DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SCOTT', 'connect'),\n         1, 'GRANTED', 0, 'DENIED', NULL) privilege\nFROM dba_network_acls;<\/code><\/code><\/pre>\n\n\n\n
Note: Replace 'SCOTT'<\/code> with your username.<\/em><\/p>\n\n\n\n
Remove ACLs and Privileges<\/h3>\n\n\n\n
-- Unassign ACL from Host\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.oracle.com');\nEND;\n\/\nCOMMIT;\n\n-- Remove Privilege from User\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(\n    acl => 'test.xml',\n    principal => 'SCOTT',\n    privilege => 'connect');\nEND;\n\/\nCOMMIT;\n\n-- Drop ACL\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'test.xml');\nEND;\n\/\nCOMMIT;<\/code><\/code><\/pre>\n\n\n\n
Troubleshooting ORA-24247 (Network Access Denied)<\/h3>\n\n\n\n
    \n
  • Verify ACL assigned for host and port using:<\/li>\n<\/ul>\n\n\n\n
    SELECT ACL, HOST, LOWER_PORT, UPPER_PORT FROM DBA_NETWORK_ACLS;<\/code><\/code><\/pre>\n\n\n\n
      \n
    • Ensure user has proper privileges (ADD_PRIVILEGE<\/code>).<\/li>\n\n\n\n
    • Confirm ACL is assigned to the correct host\/IP.<\/li>\n<\/ul>\n\n\n\n
      Common Use Cases<\/h3>\n\n\n\n
      -- Grant access to all hosts and ports\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(\n    acl => 'test.xml',\n    host => '*',\n    lower_port => 1,\n    upper_port => 9999\n  );\nEND;\n\/\nCOMMIT;\n\n-- Remove specific user privilege\nBEGIN\n  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(\n    acl => 'test.xml',\n    principal => 'HR',\n    privilege => 'resolve'\n  );\nEND;\n\/\nCOMMIT;<\/code><\/code><\/pre>\n\n\n\n
      Summary<\/h3>\n\n\n\n
      Oracle ACLs allow secure, fine-grained control over network resource access. Use the DBMS_NETWORK_ACL_ADMIN<\/code> package to create, assign, modify, and remove ACLs and privileges. Regular ACL reviews help maintain security and operational control.<\/p>\n","protected":false},"excerpt":{"rendered":"
      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 Term Description Principal User or role granted permissions Privilege Allowed operations (e.g., connect, resolve) Host Network host or IP address Ports Port range (lower and upper) View […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4172","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}