UTL_ Package Network Access Management - Oracle 11g

Even with all the security lockdown improvements in OracleDatabase 11g, Oracle still has quite a few packages that haveexecute permissions granted to PUBLIC.Based on the following query results,four particular packages pose network securityconcerns: UTL_SMTP, UTL_TCP, UTL_FILE, and UTL_HTTP.

1 select table_name, privilege
2 from dba_tab_privs
3 where (grantee = 'PUBLIC'
4 and table_name like 'UTL%')
5 and privilege = 'EXECUTE'
6* order by table_name
SQL> /
---------------------------------- ----------
24 rows selected.

As a rule,DBAs should revoke unnecessary privileges and rolesfrom PUBLIC since all database accounts have privileges granted to PUBLIC.Specifically, you should revoke the four packageslisted in Table in a standard database build.

UTL_ Package Network Access Management

Grant access to these four packages only on an as-needed basis with adequate business justification.When and if there are no options, grant access to the packages to specific database users.

Fine-Grained Access Control for UTL_* Network Packages

Packages such as UTL_TCP,UTL_SMTP,UTL_MAIL,UTL_HTTP,and UTL_INADDR provide access to network services to and from the database.If business requirements dictate that these packages become an essential element of the application, Oracle Database 11g provides a mechanism to refine the level of access.

You can use the DBMS_NETWORK_ACL_ADMIN package tofacilitate management of the UTL_* network access packages.Granting access involves several steps. First, you have to create an access control list (ACL) and configure the pri vilege definitions associated with the ACL.This is also known as defining the access control entries (ACEs). Next, you have to assign the ACL to one or more network hosts.

Create an Access Control List

All ACL definitions are stored in XML DB in the form of XML documents.The ACL XML files reside in the /sys/acls directory of the XML DB repository.ACLs can be defined using the APIs within the XML DB, but Oracle also provides DBAs with a simplecommand-line interface in the DBMS_NETWORK_ACL_ADMINpackage. Look to using the CREATE_ACL procedure to create an XML file called dba.xml:

3 acl => 'dba.xml',
4 description => 'Network Access Control for the DBAs',
5 principal => 'RODBA',
6 is_grant => TRUE,
7 privilege => 'connect',
8 start_date => null,
9 end_date => null);
10* END;
SQL> /
PL/SQL procedure successfully completed.

Within the CREATE_ACL procedure,the ACL parameter defines the file name of the ACL.When you first create the ACL,the ACL must have at least one privilege setting.

Please note that this file name is case-sensitive.The descriptionparameter is a short description of the file’s purpose.The principal parameter defines the user or role for which you want to create the ACL.The principal must be a valid entry from the DBA_USERS view or from the DBA_ROLES view.Because it is a database-authenticated account or a role, the entr must be in uppercase.In this particular example, the rodba account is in uppercase as RODBA.The is_grant parameter specifies whether you want to grant the grant connect privilege or deny the grant.This parameter is a Boolean flag.

The privilege parameter has two valid value options: connect or resolve.The package required by the application determines this value.If the user needs access to an external network computer using the UTL_TCP,UTL_HTTP, UTL_SMTP, and UTL_MAILpackages, then the connect privilegeis required.The resolve privilege is necessary if the requirement is to use the UTL_INADDR package to identify the host name based on the supplied IP address. The privilege is also casesensitive.

The start_date is an optional parameter but must be in the TIMESTAMP WITH TIME ZONE format (YYYY-MM-DD HH:MI:SS.FF TZR).The start_date defines when this ACE will become valid.The start_date will be valid starting on the start_date entry and going forward.The default value is null. An example of the start date looks like this:

start_date => '2007-07-11 10:30:00.00 US/Pacific',

The last parameter, end_date, defines the end date for the ACE.This parameter has the same format as the start_date parameter:


Since the dba.xml file is created,let’s look inside the XML repository to confirm.We can query RESOURCE_VIEW to find the dba.xml ACL in the /sys/acls directory:

SQL> SELECT any_path
2 FROM resource_view
3* WHERE any_path like '/sys/acls/dba%'
SQL> /

You need to be cautious with too many entries in the ACL. ACLs are checked for each access to Oracle XML DB repository.The ACL check operation is a critical component of the XML DB performance.The number of entries in the ACL affects the performance of the XMLDB. Oracle recommends that you share ACLs as much as possible between resources. In addition, ACL check operations perform best when the number of ACEs in the ACL is at 16 entries or less.

Add Access Control Entries

Once you create the initial ACL with the CREATE_ACL procedure, you can continue to add moreprivileges to the XML file.Use the ADD_PRIVILEGE procedure to add or revoke principals in the ACL.This example will add CKIM to the dba.xml file and grant him network access:

3 acl => 'dba.xml',
4 principal => 'CKIM',
5 is_grant => TRUE,
6 privilege => 'connect',
7 start_date => null,
8 end_date => null);
9* END;
SQL> /
PL/SQL procedure successfully completed.

Again, the ACL file name and the principal are case-sensitive.What differentiates the ADD_ PRIVILEGE procedure from the CREATE_ACL procedure is that the ADD_PRIVILEGE does not have the description API for the ACL file name.At this point, you have built the foundation by defining the ACL and adding principals and privileges to the ACL.Now, let’s assign network hosts and see this fine-grained access control for networkservices at work.

Assign Hosts

The ASSIGN_ACL procedure is used to authorize access to one or more network hosts.The network host can be internal or external to the company.In this particular example, you are allowing the database to be able to make HTTP calls to the dbaexpert.com web site.The dbaexpert.com site is a four-CPU server running Debian Linux version 2.4.

3 acl => 'dba.xml',
4 host => 'dbaexpert.com',
5 lower_port => 80,
6 upper_port => 443);
7* END;
8 /
PL/SQL procedure successfully completed.

The lower port and upper port parameters are null by default.You should preface the lower and upper port ranges for tightersecurity. The lower port defines the lower boundaries of the allowable port ranges.

The upper port defines the upper boundaries for theallowable port ranges. These port ranges should be defined only for connect privileges; they should not be set for resolveprivileges.There is another caveat to the port ranges. If you omit one of the parameters but define the other,then bothparameters take the value of the one you provided.Forexample,if you set lower_port to 80 and do not define the port value for upper_port,the upper_port parameter automatically inherits the value of 80.

Assign Hosts

Since you created the ACL, assigned privileges/principals,and authorized network entities,you can validate that the ACLpermissions worked accordingly.Take a look at the UTL_HTTP. REQUEST API to retrieve a web page from the dbaexpert.com web site to confirm that the network access rights work asplanned:

SQL> select utl_http.request('http://www.dbaexpert.com') from dual;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Database Experts</title>
<link rel="SHORTCUT ICON" href="favicon.ico"></link>
<link href="css/dbaStyle.css" rel="stylesheet" type="text/css" />
<!--Tree Menu-->
<script language="JavaScript" src="tree.js"></script>
<script language="JavaScript" src="tree_items.js"></script>
<script language="JavaScript" src="tree_items_top.js"></script>
<script language="JavaScript" src="tree_tpl.js"></script>

Since this example returns a valid HTML page,you can conclude that the ACL host assignment worked as planned.If the sufficient ACL privileges or ACL assignments are not provided,you will receive the dreaded ORA-24247 error:

SQL> select utl_http.request('http://dbaexpert.com') from dual;
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1Assign Hosts

Maintain Access Control Lists

Two more procedures to maintain the fine-grained access of UTL APIs are DROP_ACL and DELETE_PRIVILEGE.Use the DROP_ACL procedure to remove the XML file from the /sys/acls directory.Use DELETE_PRIVILEGE to remove an access control entry from the XML file.For example, if you want to remove CKIM’s ACE from the dba.xml control list,you would simply execute the following:

1 begin
2 dbms_network_acl_admin.DELETE_PRIVILEGE
3 (ACL =>'dba.xml',
5* end;
SQL> /
PL/SQL procedure successfully completed.Maintain Access Control Lists

In the same manner, if you want to delete the ACL, Oracle provides the DROP_ACL procedure.In the following example,Oracle will delete the /sys/acls/ dba.xml file from the XML DB repository:

1 begin
2 dbms_network_acl_admin.drop_acl
3 (ACL=>'dba.xml');
4* end;
SQL> /
PL/SQL procedure successfully completed.

Query Your Access Control List

There are two new data dictionary views to query the access rights for users and authorizedhosts:DBA_ACL_NETWORKS andDBA_ NETWORK_ACL_PRIVILEGE. You can query theDBA_NETWORK_ACL_PRIVILEGE view to query network privileges granted or denied for the access control list.Since start_date and end_dates are permissible for network privileges,you can query this view to see when certain users have access.

First,look at the assigned network privileges listed for the dba.xml file:

SQL> select host, lower_port, upper_port, acl
2 from dba_network_acls
3* where acl='/sys/acls/dba.xml'
SQL> /
-------------------- ----------- ----------- --------------------
dbaexpert.com 80 443 /sys/acls/dba.xml

You can see the lower and upper bounds for the open ports and that access to the dbaexpert.com has been punched for security access. The next step would be to find out who the principals are for the dba.xml ACL. Let’s see how DBAs can query theDBA_ NETWORK_ ACL_PRIVILEGES view privileges granted toprincipals:

SQL> select acl,principal, privilege,is_grant
from dba_network_acl_privileges
SQL> /
-------------------- ---------------- ----------------- ---------------
/sys/acls/dba.xml JWARD connect false
/sys/acls/dba.xml APP_SUPPORT connect true
/sys/acls/dba.xml RODBA connect true
/sys/acls/dba.xml CKIM connect true
/sys/acls/dba.xml DBA connect true
/sys/acls/dba.xml SALAPATI connect true
/sys/acls/dba.xml APPLICATION_ connect true
/sys/acls/dba.xml DSWEET connect false
/sys/acls/dba.xml RODBA connect false
9 rows selected.

we can use the following query to determine user permissions for network host connections:

SQL> select host, lower_port, upper_port, acl,
2 decode(
3 dbms_network_acl_admin.check_privilege_aclid
(aclid, 'CKIM', 'connect'),
4 1, 'GRANTED', 0, 'DENIED', null) privilege
5 from dba_network_acls
6 where HOST in
7 (select * from
8 table(dbms_network_acl_utility.domains('dbaexpert.com')))
9 order by dbms_network_acl_utility.domain_level(host) desc,
10 lower_port, upper_port
SQL> /
--------------- ----- ----- ----------------- ----------------
dbaexpert.com 80 443 /sys/acls/dba.xml GRANTEDQuery Your Access Control List

Users can check their access rights to the network and domain by querying the USER_NETWORK_ACL_PRIVILEGES view.This view is granted to the public,so all database users have select access to it.By logging in with your session,you can query the view to see that your access entry exists in the dba.xml file and that you have been granted rights to the dbaexpert.com web site.For example:

SQL> select host, lower_port, upper_port, status privilege
2 from user_network_acl_privileges
3 where host in
4 (select * from
5 table(dbms_network_acl_utility.domains('dbaexpert.com'))) and
6 privilege = 'connect'
7* order by dbms_network_acl_utility.domain_level(host) desc, lower_port
SQL> /
--------------- ---------- ----------- ---------
dbaexpert.com 80 443 GRANTED

Order Your Access Control List

The order of the access control entries in the ACL play animportant role in security. Oracle reads the access entries from the top down. If the first entry is a role that allows access to the network but the second entry rest ricts you from the network, you will still be able to access the network because you are part of the first role that allows you access to the network.If you were denied from the network in the first entry but granted access to the network through a role in the second entry, you would be denied access because of the order precedence.

You can use the position parameter of the ADD_PRIVILEGEprocedure to specify the order in the ACL.In this example,the principal JWARD is placed at the beginning of the ACL with the deny flag and with a position value.

  1. Next,the APP_SUPPORT database role is granted network access with a position value of.
  2. Even though the JWARD user account has theAPP_SUPPORT database role assigned to him, he will nothave access because the JWARD principal is deniedthenetwork access privilege prior to the APP_SUPPORT role in the ACL:
position => 1,
acl => 'dba.xml',
principal => 'JWARD',
is_grant => FALSE,
privilege => 'connect',
start_date => null,
end_date => null);
position => 2,
acl => 'dba.xml',
principal => 'APP_SUPPORT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);

In the dba.xml file,you will see the order of the privilege assignment or denial.A <grant>XML tag with a false valueindicates that the network access is denied.This is evidenced in the output of the dba.xml file for JWARD:

- <a:acl description="Network Access Control for the DBAs"
xmlns:a="http://xmlns.oracle.com/xdb/acl. xsd"
xsi:schemaLocation="http://xmlns.oracle. com/xdb/acl.xsd
http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
- <a:ace xmlns:a="http://xmlns.oracle. com/xdb/acl.xsd">
- <a:privilege>
<plsql:connect xmlns:plsql="http://xmlns.oracle. com/plsql" />
- <a:ace xmlns:a="http://xmlns.oracle. com/xdb/acl.xsd">
- <a:privilege>
<plsql:connect xmlns:plsql="http://xmlns. oracle.com/plsql" />
- <a:ace>
+ <a:privilege>
<plsql:connect xmlns:plsql="http://xmlns.oracle. com/plsql" />

sysasm Privilege for Automatic Storage Management

The sysasm system privilege is added to Oracle Database 11g to provide enhanced security for ASM.This privilege delineates the roles and responsi bility of the ASM administrator.Now,either the system administrator or the SAN administrator can manage the ASM instance.Starting from Oracle Database 11g, Oracle recommends that customers use the sysasm role instead of the sysdba role to administer ASM instances.

LOB Encryption Enhancements

SecureFiles is a completely reengineered LOB storage thatpromises performance, security compression, and encryption. The introduction of the Secure Files encrypts the new LOBs in the latest encryption algorithms.Secure Files provide encryption capabilities while providingadditional capabilities such as compression and deduplication. For all the new develop ments in Oracle LOB encryption.

Data Pump Encryption

Oracle Database 11g introduces the encryption of Data Pump dump files.In the previousrelease, only the metadata could be encrypted at AES-128,AES-192,and AES-256 modes.In addition, Data Pump provides APIs to remap data during the export and import operations.The combination of both encryption and data remappingcapabilities provide a significant level of protection in this new release. For details of all the new enhancements related to Data Pump.

RMAN Virtual Private Catalog

In Oracle Database 11g, you can restrict access to the recovery catalog by granting access to only a subset of the metadata in the recovery catalog.You can create a subset virtual catalog for groups and database users.The subset that a user has read/write access to is termed a virtual private catalog,or just virtual catalog.The central or source recovery catalog is now also called the base recovery catalog.For complete details on RMAN virtual private catalogs.

RMAN Backup Shredding

Backup shredding is a key management feature available in Oracle Database 11g.DBAs can destroy encryption keys of TDE backups without having physical access to backup media.By destroying the encryption keys,this allows DBAs to renderbackups inaccessible.For complete details on backup shredding.

TDE with LogMiner Support

Oracle Database 11g’s enhances LogMiner to supportTDE.Oracle Logical Standby relies on LogMiner to examine the redo logs to generate SQL statements for the SQL Applyprocess.The wallet must be open to decrypt encryptedcolumns.To open thewallet, the database must be in a mounted state to populate the V$LOGMNR_CONTENTS view.

TDE with Data Guard SQL Apply

LogMiner TDE support allows logical standby to supportTDE.Oracle’s logical standby databas has several datatypelimitations.Prior to Oracle Database 11g,if the primary database supported any kind of encryption using TDE, the logical standby database option for data protection had to be dismissed.Starting with Oracle Database 11g,the logical standby database fully supports TDE.This support enables a logical standbydatabase to be acceptable by more customers and a widervariety of applications,especially in companies with advanced security requirements.For complete details relative to TDE and Data Guard Logical Standby.

Oracle Audit Vault

In a nutshell, Oracle’s Audit Vault is a secure database to store and analyze audit information collected from other databases and OS audit trails. Oracle Database 11g enhances Streams support for the Audit Vault.Oracle Streams achieves support for Audit Vault by allowing the Audit Vault to control the Streams configuration.When you integrate Streams with the Audi Vault,you will be prohibited at the source database from making changes.

Furthermore,Oracle Streams introduces the functionality tocapture changes introduced by the sys and system database accounts.This allows for Oracle Streams to forward information to the Audit Vault database audit trail.

TDE with Hardware Security Module Integration

The majority of Oracle’s security enhancements engage theOracle Wallet has to be open to allow the key to encrypt thedatabase columns and tablespaces. For the majority of thecompanies,Oracle Wallet implementation is adequate.Forcompanies that necessitate more stringent securityrequirements,hardware solutions can provide the supplemental protection.

In a TDE implementation,Oracle’s master key must reside in memory to perform cryptographic operations.Hackers can attack Oracle by dumping the physical system memory to retrieve the key.To mitigate this security risk,Oracle broadens TDE functio nality,allowing hardware security modules (HSMs) to be used for enhanced physical and logical protection of the masterkeys. Becauseencryption and decryption functions using themaster key occur inside the HSM,it prevents exposure of the master key to a server’s memory.

HSMs provide secure computational space (memory) to perform enc ryption and decryption algorithms.HSM implementation is a secure alternative to Oracle Wallet.

Oracle Database 11g TDE integrates with hardware solutions such as Ingrian to provide the highest level of security for TDE. Ingrian is a hardware-based securityappliance that provides centralized encryption capabilities for application and database data.Additionally,Ingrian stores all the encryption keys in its hardware appliances and offloads theencryption overhead off the application and database
servers.Security administ rators consider key management an integral component to corporate security.It is a security risk to have encryption keys stored in the database,filesystems,applications,or web servers.

This is so because so many people have access to all these layers.With HSM,the keys are not stored in an operating system but at the physical device.

You can configure HSMs such as Ingrian to be tamper-resistant.Ingrian is certified to FIPS 140-2 Level 3,the widely accepted standard of government-specified best practices for network security.Private keys are generated and stored inencrypted form within the HSM.Keys stored in the HSM areprotected from physical attacks and cannot be compromised even by stealing the Ingrian appliance.Attempts made totamper with or probe the card will result in the immediate destruction of all private key data, making it virtually impossible for either external or internal hackers to access this vitalinformation.1

The HSM product will be configured by the security administrator. Once the HSM product is configured successfully, you can proceed to modify your sqlnet. ora file and change the METHOD value. Earlier in the chapter,you learned how to create a wallet and the method of the wallet in the operating system desi gnated as a file.When you set up an HSM product,you must assign the METHOD option the value of HSM, as shown here:


The DIRECTORY value in this example is not required by the HSM but may be by other Oracle products such as RMAN.The DIRECTORY option is also mandatory when migrating from a software-based wallet.The DIRECTORY path is used to locate the old wallet file.

Your vendor will provide the appropriate PKCS#11 (an APIdefining a generic interface to crypto graphic tokens) libraryfile.TDE integration with HSM must utilize this library file provided by the vendor to interface with the HSM. You will need to copy the library file to a location in your operating system accessible to the database server.A new database user is also required for the database tocommunicate with the HSM.Once done, you will have to generate the master encryption key for the HSM and open the new wallet with the designated user ID and password using the following syntax:

alter system set encryption key identified by user_Id:password;

You can optionally use the MIGRATE USING wallet passwordclause if you are migrating from an existing software-basedwallet.The MIGRATE clause will decrypt the existing columnencryption keys and then encrypt them with the newly created, HSM-based master encryption key.There will be additional setup requirements provided by the HSM vendor to integrate Oracle TDE with HSM.Once the HSM and TDE integration is complete, HSM can be used just like any othersoftware wallet.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics