What has been changed in Oracle Database 12c with Network ACLs?
Starting from 12c, network access control in the Oracle database is implemented using Real Application Security access control lists (ACLs). Existing 11g network ACLs in XDB will be migrated. Existing procedures and functions of the DBMS_NETWORK_ACL_ADMIN PL/SQL package and catalog views have been deprecated and replaced with new equivalents
In 12c, a network privilege can be granted by appending an access control entry (ACE) to a host ACL using DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE. If you append an ACE to a host that has no existing host ACL, a new host ACL will be created implicitly. If the host ACL already exists, the ACE will be appended to the existing ACL.
(both paragraphs taken from MOS Note: 2078710.1)
What happens during/after upgrade?
- Existing network ACLs will be migrated from XDB in Oracle 11g to Real Application Security in Oracle 12c.
All privileges of the existing ACLs will be preserved - Existing ACLs will be renamed
- Mapping between the old / new names is reflected in DBA_ACL_NAME_MAP.
Issues before/during Database Upgrade?
First of all the current preupgrd.sql does not warn you correctly if such ACLs exist. This fix gets added to the preupgrd.sql. But you’ll need to download the most recent version from MOS Note 884522.1. The one from January 2015 does not have it yet. But this is addressed and will be implemented soon.
Here’s an issue which happened to one of my very experienced colleagues from Oracle Consulting in an upgrade project:
“Customer had network ACLs defined and Privileges (resolve,connect) granted for several hosts to several DB
users in 11.2.0.3.With the first DB, we observed the ACL renaming as you described it, but, much worse: 4 out of 9 privileges granted
were completely gone away after the upgrade performed by DBUA (to 12.1.0.2.4). We then were able to evaluate the missing privileges and re-grant them again. Warned by that, for the next databases to be upgraded, we copied all the 11.2.0.3 content of the DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES to helper tables in order to be able to restore lost privileges (which was a good idea, as in one of the databases, only 87 out of 240 formerly existing privileges survived the upgrade).”
Solution?
Check for existing Network ACLs before the upgrade or get the most recent preupgrd.sql once it contains the check.
Preserve the existing network ACLs and privileges (DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES) in a intermediate staging table to have the possibility to restore them afterwards in case the automatic migration fails or does not happen.
If you encounter a situation where your Network ACLs don’t get migrated correctly, disappear and/or don’t exist in the mapping table DBA_ACL_NAME_MAP afterwards please open an SR and let Oracle Support check. There are known issues with mappings and migrations not done correctly (find some bugs below) so needs to be verified if you have hit a known issue or encountered a new one.
More Information?
- MOS Note: 2078710.1
Changes in Network ACLs after Upgrading from Oracle DB 11g to 12c
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2078710.1 - Bug# 22061588
PREUPGRADE TOOL DOES NOT ALERT ABOUT THE NETWORK ACL MIGRATION IN 11.X TO 12C - Patch# 17532734
ORA-28104: INPUT VALUE FOR DB USER OR ROLE IS NOT VALID ON UPGRADE
https://support.oracle.com/epmos/faces/PatchDetail?patchId=17532734&requestId=19304630 - Bug# 20369415
UPGRADE TO 12C FAILS – XDB ERROR ORA-1830 ORA-6512: AT “SYS.XS_OBJECT_MIGRATION
https://support.oracle.com/epmos/faces/BugDisplay?id=20369415 - Oracle Database 12c – Security Guide
Changes to Configuring Fine-Grained Access to Services and Wallets
https://docs.oracle.com/database/121/DBSEG/release_changes.htm#BABGCGFE - Oracle Database 11g – Security Guide
Managing Fine-Grained Access to External Network Services
https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG40012 - Oracle Database 11g/12c- App Developer Guide
DBMS_NETWORK_ACL_ADMIN Package
11g: https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm
12c: https://docs.oracle.com/database/121/ARPLS/d_networkacl_adm.htm#ARPLS148 - Oracle Database 12c – App Developer Guide
DBMS_NETWORK_ACL_ADMIN – Deprecated Subprograms:
https://docs.oracle.com/database/121/ARPLS/d_networkacl_adm.htm#ARPLS74569 - Oracle Base (Tim Hall) – Fine Grained Access to Network Services in Oracle 11.1
https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1 - Oracle Base (Tim Hall) – Fine Grained Access to Network Services in Oracle 12,1
https://oracle-base.com/articles/12c/fine-grained-access-to-network-services-enhancements-12cr1
- Pythian (Don Seiler) – Setting Up Network ACLs in Oracle 11g for Dummies
http://www.pythian.com/blog/setting-up-network-acls-in-oracle-11g-for-dummies/
–Mike
oracle referenced a link https://blogs.oracle.com/UPGRADE/entry/network_acls_and_database_upgrade,
because we opened an sr concerning problems we are having
with an existing smtp package that it returning an error
ERROR: ORA-29279: SMTP permanent error: 500 Unrecognized command..
we’ve been using the package for the last 3-5 yrs, and the only thing we changed was the smtp_host value to VARCHAR2(4000) := ‘express-relay.jangosmtp.net’;
, it was ‘relay.jangostmp.net’. The package still works with
relay.jangosmtp.net, but our relay provider has adivsed us that in the future, we will no longer be able to use it..
We’re trying to understand why changing the name would be causing this error ?
Please check this with Oracle Support – I can’t tell you why this may cause an error and if this is expected or unexpected (most likely the latter is true). But I can’t tell you the reason but Support should be able to find out.
Cheers
Mike
the problem turned out to be the relay server domain name also changed and we didn’t know that, once we changed the domain name in our smtp package, the error went away.