Hello everyone,
it’s been a while since my last post. So, I wanted to write about export and import ACL privileges. They always become a pain (at least for me). after 12c, Oracle introduce us a new and easy way of ACL copying from db to db. export&import.
so here is the basic command on your source database:
|
1 |
expdp system directory=DATA_PUMP_DIR logfile=ACL_EXPORT.log dumpfile=ACL_EXPORT.DMP full=y include=NETWORK_ACL |
this will export all stored ACL privileges on your database and to import those ACLs to a new db:
|
1 |
impdp system directory=DATA_PUMP_DIR logfile=ACL_IMPORT.log dumpfile=ACL_EXPORT.DMP |
so easy but during the import you can get ORA-24244: invalid host or port for access control list (ACL) assignment error.
I encountered this error while creating a new db for a client. In my case this has happened because RESOLVE privileges shouldn’t be used with port definition!
so, somehow previous db has port definitions for RESOLVE privileges and those ones wasn’t imported. to add them into new db, I simply used this sql:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select 'BEGIN DBMS_NETWORK_ACL_ADMIN.append_host_ace ( host => '''|| t1.host||''', ace => xs$ace_type(privilege_list => xs$name_list(''RESOLVE''), principal_name => '''||t2.principal||''', principal_type => xs_acl.ptype_db)); end; /' run_Script from dba_network_acls t1 join dba_network_acl_privileges t2 on t1.aclid = t2.aclid where privilege='resolve' and lower_port is not null; |
run this select on the source db and execute the output on the target db.
wish you all happy and healthy days.