{"id":1621,"date":"2025-01-10T08:40:39","date_gmt":"2025-01-10T08:40:39","guid":{"rendered":"https:\/\/w3buddy.com\/?p=1621"},"modified":"2026-01-15T13:19:19","modified_gmt":"2026-01-15T07:49:19","slug":"exploring-access-control-lists-acl-privileges-in-oracle","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/exploring-access-control-lists-acl-privileges-in-oracle\/","title":{"rendered":"Exploring Access Control Lists (ACL) Privileges in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Key Features of Oracle ACLs<\/strong><\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Fine-Grained Access Control<\/strong>: ACLs provide security for both table data and Oracle XML DB repository resources.<\/li>\n\n\n\n<li><strong>User Types<\/strong>: Supports Oracle Fusion users (non-database users).<\/li>\n\n\n\n<li><strong>ACL Storage<\/strong>: ACLs are stored in XML format under <code>\/sys\/acl\/<\/code> in the Oracle XML DB Repository.<\/li>\n\n\n\n<li><strong>Key Dimensions<\/strong>:\n<ul class=\"wp-block-list\">\n<li><strong>Principals<\/strong>: Specify which users or roles are granted access.<\/li>\n\n\n\n<li><strong>Privileges<\/strong>: Define allowed operations, e.g., <code>connect<\/code>, <code>resolve<\/code>.<\/li>\n\n\n\n<li><strong>Objects<\/strong>: Indicate the specific data or resources the permissions apply to.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Viewing Existing ACLs<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To inspect the ACLs and associated permissions:<\/p>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Set output formatting\nSET PAGESIZE 50;\nSET LINESIZE 150;\nSET COLSEP ' | ';\nSET FEEDBACK ON;\n\n-- Format columns for better readability\nCOLUMN ACL_OWNER FORMAT A15;\nCOLUMN ACL FORMAT A50;\nCOLUMN HOST FORMAT A30;\nCOLUMN LOWER_PORT FORMAT 9999;\nCOLUMN UPPER_PORT FORMAT 9999;\nCOLUMN PRINCIPAL FORMAT A20;\nCOLUMN PRIVILEGE FORMAT A15;\n\n-- Query to view network ACLs\nSELECT ACL_OWNER, ACL, HOST, LOWER_PORT, UPPER_PORT \nFROM DBA_NETWORK_ACLS;\n\n-- Query to view ACL privileges\nSELECT ACL_OWNER, ACL, PRINCIPAL, PRIVILEGE \nFROM DBA_NETWORK_ACL_PRIVILEGES;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Output: <\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">ACL_OWNER       | ACL                                                | HOST                           | LOWER_PORT | UPPER_PORT\n--------------- | -------------------------------------------------- | ------------------------------ | ---------- | ----------\nSYS             | \/sys\/acls\/oracle-sysman-ocm-Resolve-Access.xml     | localhost                      |            |\nSYS             | NETWORK_ACL_3D6A1A8749CB43C0AB17CF3BCB6CDE04       | *                              |            |\n\n2 rows selected.<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Creating and Managing ACLs<\/strong><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">1. <strong>Create an ACL<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\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;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">2. <strong>Assign ACL to a Network Host<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\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;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">3. <strong>Add Privileges for Another User<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\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><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">4. <strong>Check Assigned Permissions<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Set output formatting\nSET PAGESIZE 50;\nSET LINESIZE 150;\nSET COLSEP ' | ';\nSET FEEDBACK ON;\n\n-- Format columns for better readability\nCOLUMN HOST FORMAT A30;\nCOLUMN LOWER_PORT FORMAT 9999;\nCOLUMN UPPER_PORT FORMAT 9999;\nCOLUMN ACL FORMAT A50;\nCOLUMN PRIVILEGE FORMAT A10;\n\n-- Query to view ACLs with privileges\nSELECT host, lower_port, upper_port, acl,\n  DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SYS', 'connect'),\n         1, 'GRANTED', 0, 'DENIED', NULL) privilege\nFROM dba_network_acls;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Note &#8211; Replace <strong>SYS<\/strong> with your actual username for which you are checking in above query.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Output:<\/h2>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">HOST                           | LOWER_PORT | UPPER_PORT | ACL                                                | PRIVILEGE\n------------------------------ | ---------- | ---------- | -------------------------------------------------- | ---------------\nlocalhost                      |            |            | \/sys\/acls\/oracle-sysman-ocm-Resolve-Access.xml     | GRANTED\n*                              |            |            | NETWORK_ACL_3D6A1A8749CB43C0AB17CF3BCB6CDE04       | GRANTED\n\n2 rows selected.<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">5. <strong>Unassign ACL from a Host<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\n  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.oracle.com');\nEND;\n\/\nCOMMIT;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">6. <strong>Remove Privileges for a User<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\n  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(\n    acl => 'test.xml',\n    principal => 'SCOTT',\n    privilege => 'connect');\nEND;\n\/\nCOMMIT;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">7. <strong>Drop an ACL<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\n  DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'test.xml');\nEND;\n\/\nCOMMIT;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Troubleshooting ORA-24247 (Network Access Denied)<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If you encounter the error ORA-24247: network access denied by access control list (ACL):<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Verify the ACL setup for the host and port:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">-- Set output formatting\nSET PAGESIZE 50;\nSET LINESIZE 150;\nSET COLSEP ' | ';\nSET FEEDBACK ON;\n\n-- Format columns for better readability\nCOLUMN ACL FORMAT A50;\nCOLUMN HOST FORMAT A30;\nCOLUMN LOWER_PORT FORMAT 9999;\nCOLUMN UPPER_PORT FORMAT 9999;\n\n-- Query to view network ACLs\nSELECT ACL, HOST, LOWER_PORT, UPPER_PORT \nFROM DBA_NETWORK_ACLS;<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>Ensure the user has the necessary privileges using DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE.<\/li>\n\n\n\n<li>Confirm the ACL is assigned to the required host or IP.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Common ACL Management Scenarios<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Granting all hosts access:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\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;<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Removing a specific user privilege:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">BEGIN\n  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(\n    acl        => 'test.xml',\n    principal  => 'HR',\n    privilege  => 'resolve'\n  );\nEND;\n\/\nCOMMIT;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Summary<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Oracle ACLs offer a robust way to manage network access permissions. Using the <code>DBMS_NETWORK_ACL_ADMIN<\/code> 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.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For more information, you can refer to the official Oracle documentation on configuring application privileges and Access Control Lists (ACLs) <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/dbfsg\/configuring-application-privileges-and-access-contol-lists.html#GUID-F6C92BDA-15C8-4515-BDED-9CCB2BC02985\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 Viewing Existing ACLs To inspect the ACLs and associated permissions: Output: Creating and Managing ACLs 1. Create an [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-1621","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1621","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=1621"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1621\/revisions"}],"predecessor-version":[{"id":1623,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1621\/revisions\/1623"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=1621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=1621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=1621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}