Is it possible to name an ACL

oracleoracle-12c

I need to create some ACL's to allow TCP connections to some hosts. These hosts can only be accessed by their IP address. To make them easier to keep track of in the future, I'd like to be able to give the ACL some kind of friendly name, but I can't see a way of doing it.

I'm on Oracle 12.2, and from reading the DBMS_NETWORK_ACL_ADMIN documentation it sounds like I'm supposed to use the dbms_network_acl_admin.append_host_ace procedure. I've created the ACL with (details changed to protect the innocent):

BEGIN
    dbms_network_acl_admin.append_host_ace(
        host => '10.10.10.10',
        lower_port => NULL,
        upper_port => NULL,
        ace => xs$ace_type(
            privilege_list => xs$name_list('connect'),
            principal_name => 'USER1',
            principal_type => xs_acl.ptype_db
        )
    );
END;

This appears to have created it OK, but when I look at DBA_NETWORK_ACL_PRIVILEGES, I'm hoping to find some way of making perhaps the ACL field more friendly (I can understand it having an arbitrary ACLID, but it'd be nice to able to one field customisable!)

SQL> SELECT * FROM dba_network_acls WHERE host = '10.10.10.10';

HOST        LOWER_PORT UPPER_PORT ACL                                          ACLID            ACL_OWNER
----------- ---------- ---------- -------------------------------------------- ---------------- ---------
10.10.10.10                       NETWORK_ACL_780DE78AA2A6289CE0531240D80A83F6 0000000080002774 SYS

Best Answer

This may be a bit unorthodox, but you could have a table that allows you to store "friendly names", and comments etc, which you can update/query whenever required. Eg

create table myfriendlynames (
  id number generated always as identity primary key 
, aclid varchar2( 256 ) unique
, friendlyname varchar2( 256 ) unique
--, comments varchar2( 256 )
) ;

-- pick up all aclids from dba_network_acls, and store them in the new table
merge into myfriendlynames M
using (
  select aclid from dba_network_acls
) ACLS on ( M.aclid = ACLS.aclid )
when not matched then
  insert ( M.aclid ) values ( ACLS.aclid )
;

Update, set a friendly name ...

SQL> select * from myfriendlynames ;
ID  ACLID             FRIENDLYNAME  
1   0000000080002710  NULL   

update myfriendlynames
set friendlyname = 'Fred'
where friendlyname is null ;  -- you could also use id here

-- aclid - friendlyname mapping
select ACLS.aclid, M.friendlyname
from dba_network_acls ACLS
  join myfriendlynames M on ACLS.aclid = M.aclid
;

ACLID             FRIENDLYNAME  
0000000080002710  Fred

Query / view

-- add/remove columns here as required
select 
  ACLS.host
, ACLS.lower_port
, ACLS.upper_port
, substr( ACLS.acl, 1, 16 ) || '...' || substr( ACLS.acl, -4, 4 ) aclshort
, ACLS.aclid
, M.friendlyname
from dba_network_acls ACLS
  left join myfriendlynames M on ACLS.aclid = M.aclid
;

HOST  LOWER_PORT  UPPER_PORT  ACLSHORT                 ACLID             FRIENDLYNAME  
*     NULL        NULL        NETWORK_ACL_FD9A...FB6F  0000000080002710  Fred
Related Question