Oracle11g introduces several new features that are quite useful. Most of the features are available as soon as you upgrade to 11g, but few of them would require additional configuration in 11g. If you are not prepared, then these specific new features, that I will discuss in this blog, could break your applications. Fortunately, these new 11g features are not bugs! They are just features!
For smooth and flawless migration, you need to know the new features, you need to know the impact of these new features on your application, if any, and then you need to take appropriate mitigating steps. Otherwise, they could cause havoc the day-after 11g upgrade. In this blog, I will shed light on two such features that could break your application. One of them is Network Access Control List and the second one is case sensitive passwords.
Network Access Control List
Overview
Oracle database comes with several PL/SQL packages such as UTL_TCP, UTL_HTTP, UTL_MAIL, UTL_SMTP and UTL_INADDR that access network services through back-end Oracle database. UTL_MAIL is widely used to send emails from PL/SQL code. UTL_SMTP and UTL_TCP packages allow you to access back-end network resources over TCP/IP protocol. UTL_INADDR package resolves server names and IP addresses. These PL/SQL Network packages are quite useful when properly used, but outright dangerous if misused.
Oracle's Network PL/SQL packages provide direct access to network resources which are behind firewall and protected by several layers of security controls. In fact, these packages are so powerful that security auditors would closely review access granted on Network PL/SQL packages. Database security vulnerability scanners would raise a red-flag if you have granted access on Network PL/SQL packages to users or roles. By default, Oracle grants EXECUTE privilege on Network PL/SQL packages to PUBLIC. Not a good security practice at all. Therefore, DBAs would revoke PUBLIC access on Network PL/SQL packages and grant EXECUTE privilege to those who need it.
Sure, with controlled grants on Network PL/SQL packages, Oracle DBAs would reduce the security risk level, but the risk still lingers for potential security breaches. If you have access to Network PL/SQL packages, then you can access network resources that you are supposed to, and beyond. From a browser or a client outside the firewall, you potentially can access resources behind firewall.
To further tighten security on PL/SQL Network packages, Oracle11g introduces fine-grained access controls to these PL/SQL packages. With fine-grained access controls through Network Access Control Lists, you define what network resources should be accessed by the users. You can't use any SMTP server available on the back-end network. You can only use the SMTP server that you have been specifically granted access to. Oracle Network Access Control Lists define what network resources are available and accessible. An vastly improved security posture.
Oracle provides DBMS_NETWORK_ACL_ADMIN package to manage Network ACLs. A brief description on DBMS_NETWORK_ACL_ADMIN is in order as this package is little bit confusing. As explained below, a Network ACL is comprised of a list of network resources that you want to grant access to. For each network resource, you include server names and port range through upper and lower port numbers. A Network ACL is assigned to a user. A user can have connect or resolve privilege. Connect privilege includes resolve privilege. Resolve privilege is needed for UTL_INADDR package, but grant connect privilege for other Network PL/SQL packages.
Configuration
Configuration of Network Access Control List is relatively simple. There are two configuration steps:
Configure XML DB
exec UTL_MAIL.SEND (sender=>'me@mydomain.com', recipients=>'you@gmail.com', subject=>'Test Message', Message=>'test');
In response to the above command, if you get the following error, then you need to configure XML DB.
ORA-24248: XMLDB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1
Let's verify that XML DB has not been installed.
SQL> SELECT COMP_NAME, STATUS FROM DBA_REGISTRY ;
COMP_NAME STATUS
---------------------------------------- ---------------------------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Login / as sysdba to install XML DB.
sqlplus / as SYSDBA
Rem DESCRIPTION
Rem Creates the tables and views needed to run the XDB system
Rem Run this script like this:
Rem catqm.sql <XDB_PASSWD> <TABLESPACE> <TEMP_TABLESPACE> <SECURE_FILES_REPO>
Rem -- XDB_PASSWD: password for XDB user
Rem -- TABLESPACE: tablespace for XDB
Rem -- TEMP_TABLESPACE: temporary tablespace for XDB
Rem -- SECURE_FILES_REPO: if YES and compatibility is at least 11.2,
Rem then XDB repository will be stored as secure files;
Rem otherwise, old LOBS are used. There is no default value for
Rem this parameter, the caller must pass either YES or NO.
Rem NOTES
Rem Must be run connected as SYS
SQL> @$ORACLE_HOME/rdbms/admin/Catqm xdb xdb temp NO
Let's double-check that XML DB has been installed.
SQL> SELECT COMP_NAME, STATUS FROM DBA_REGISTRY ;
COMP_NAME STATUS
---------------------------------------- ---------------------------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle XML Database VALID
exec UTL_MAIL.SEND (sender=>'me@mydomain.com', recipients=>'you@gmail.com', subject=>'Test Message', Message=>'test');
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 1
As you could see from the error message, we need to configure Network Access Control List (ACL). The steps are described below.
There are few important that little details that you should keep in mind.
Don't forget to COMMIIT after you execute DBMS_NETWORK_ACL_ADMIN package.
Parameters to DBMS_NETWORK_ACL_ADMIN are case sensitive.
You can grant access on network resources to users or roles. If you are planning to execute network packages within the context of PL/SQL packages, then you must grant privileges on network resources directly to users, not roles.
-- let's configure network ACL to fix ORA-24247 errors
-- Create a network ACL called Email.xml and grant this list to user JMEHTA with connect privilege
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl=>'Email.xml', description=>'Email Access', principal=>'JMEHTA', is_grant=>TRUE, privilege=> 'connect');
-- Grant Email.xml ACL to JMEHTA1 user with connect privilege
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'Email.xml', principal=>'JMEHTA1', is_grant=>TRUE, privilege=> 'connect');
-- Add server to ACL with port range
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'Email.xml', host=>'mysmtpserver.mydomain.com', lower_port=>25, upper_port=>25);
COMMIT;
-- Data Dictionary View
select * from dba_network_acls ;
Few additional useful commands:
-- Use wildcards to grant access on all network resources within a domain
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'Email.xml', host=>'*.mydomain.com');
-- Unassign servers
EXEC DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host=>'*.mydomain.com');
EXEC DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host=>'mysmtpserver');
-- Drop a Network ACL
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl=>'Email.xml');
COMMIT;
Now, test email should work.
Case-sensitive Network Access Control Lists
Passwords in Oracle11g are case-sensitive. You may need to disable password case sensitivity by disabling the following parameter.
SEC_CASE_SENSITIVE_LOGON=false
