Query to find users granted an ACL -- the natural question after seeing ORA-24247
1 min read
You may have encountered ORA-24247: network access denied by access control list (ACL) and wondered "who has access to what from my database?" I extended a query from the Oracle documentation to give me the results I wanted: ACL Name, Username, host, lower port, upper port, and if granted connect and resolve.
with privs as (
SELECT acl, u.username, host, lower_port, upper_port,
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'connect'),
1, 'GRANTED', 0, 'DENIED', null) conn_privilege,
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'resolve'),
1, 'GRANTED', 0, 'DENIED', null) res_privilege
FROM dba_network_acls a, dba_users u
where conn_privilege is not null
or res_privilege is not null
order by acl, username
It's nothing special, but can be a handy query.