Hello everyone,
So you wanted to migrate your ACLs to another database or you are trying to add a new acl entity but you got ORA-24244. There are many reasons for that error unfortunately. I wish Oracle provides us more detail about what is the error but I wanted to share what I have seen so far and how to generate acl records. What I am about to write is for 19c databases.
- First of all, of course, you can get this error by setting an invalid port like -123 or 999999999. check them first.
- secondly, port range you use might intersect with another definition. for example, there is an acl entry for www.example.com for 3000 and 3010 ports. you can not add a new port range for same host, same principal and same privileges with port range 3010-3015. since 3010 is already defined in first entry you can not use it again, second entry must start with 3011 at least.
- thirdly, you might be trying to insert an entry for RESOLVE privilege with port definition! this is not also allowed. for resolve privilege, lower and upper ports must be null.
these are my basic steps to check. even expdp utility with “include=NETWORK_ACL” option might failed while importing them because of these 3 steps. Lately I was copying acls from a 12c db to 19c and after getting too much error I decided to generate a simple select statements to generate acls append command. you can either copy, paste and run the result of statement or run it in a loop and run the command with dynamic sql.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
with maindata as ( select /*+ materialize */ t1.host, Decode(t2.privilege, 'resolve', null, lower_port) lower_port, Decode(t2.privilege, 'resolve', null, upper_port) upper_port, t2.principal, t2.privilege, t2.is_grant from dba_network_acls t1 join dba_network_acl_privileges t2 on t1.aclid = t2.aclid where principal in (select username from dba_users where oracle_maintained = 'N') ), port_by_port as ( /* generate rows for every port */ select distinct host, principal, privilege, is_grant, p.port from maindata m left join lateral (select m.lower_port+level-1 port from dual connect by level <= m.upper_port-m.lower_port+1) p on 1=1 ), port_groups as ( /* assign a group id for consecutive port numbers.*/ select f.*, port - row_number() over(partition by host, principal, privilege order by port) port_group_id from port_by_port f ), port_ranges as ( select host, principal, privilege, port_group_id, min(port) lower_port, max(port) upper_port from port_groups group by host, principal, privilege, port_group_id ) select p.*, 'BEGIN DBMS_NETWORK_ACL_ADMIN.append_host_ace ( host => '''|| host||''', lower_port=> '|| Decode(privilege, 'resolve', 'null', nvl(to_char(lower_port), 'null'))||', upper_port=> '|| Decode(privilege, 'resolve', 'null', nvl(to_char(upper_port), 'null'))||', ace => xs$ace_type(privilege_list => xs$name_list('''||privilege||'''), principal_name => '''||principal||''', principal_type => xs_acl.ptype_db) ); end; /' acl_script from port_ranges p; |
Please don’t judge for not using match_recognize. I just wrote it fastest way I can think of. script is first dividing port ranges to row by row single ports and them regroup them (some old entries had intersected port ranges). also uses NULL for resolve privileges. I didn’t add all options of course. I hope this script helps you.
wish you healthy days.