Thursday, June 23, 2016

Query to find users granted an ACL -- the natural question after seeing ORA-24247

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, 
     DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, u.username, 'connect'),
            1, 'GRANTED', 0, 'DENIED', null) conn_privilege,
     DECODE(
         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
     )
select *
  from privs
  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.

No comments: