如何安装DBMS_NETWORK_ACL_ADMIN包？-白红宇

如何安装DBMS_NETWORK_ACL_ADMIN包？

若安装了 XDB组件，则DBMS_NETWORK_ACL_ADMIN会自动安装。若安装了XDB组件，但是

DBMS_NETWORK_ACL_ADMIN不可用，则可以单独执行如下的脚本进行安装：

点击(此处)折叠或打开

sqlplus / as sysdba

run ?/rdbms/admin/catnacl.sql

run ?/rdbms/admin/dbmsnacl.sql

run ?/rdbms/admin/prvtnacl.plb

执行如下脚本可以判断是否已经安装了XDB组件：

SELECT SCHEMA,COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY WHERE COMP_NAME LIKE '%Oracle XML Database%';

官网：

When you create access control lists for network connections, you should create one access control list dedicated to a group of common users, for example, users who need access to a particular application that resides on a specific host computer. For ease of administration and for good system performance, do not create too many access control lists. Network hosts accessible to the same group of users should share the same access control list.

简单点说：Oracle允许使用几个PL/SQL API（UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP和 UTL_INADDR）访问外部网络服务。需要进行权限授权才可以，比如需要通过oracle发送邮件。

下面是几个常用的定义acl的相关方法：

1. 创建访问控制列表

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',

description => 'WWW ACL',

principal => 'HR', --

赋予权限给哪个用户

is_grant => true, -- true表示授予权限 false表示取消权限

privilege => 'connect');

2. 使用ADD_PRIVILEGE存储过程将其他的用户或角色添加到访问控制列表中，它的参数与CREATE_ACL存储过程的参数类似，

省略了DESCRIPTION参数，同时增加了POSITION参数，它用于设置优先顺序。

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',

principal => 'HR',

is_grant => true,

privilege => 'resolve');

3.使用ASSIGN_ACL存储过程给网络分配访问控制列表

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',

host => '*.qq.com'); --主机名,域名,ip地址或分配的子网，主机名大小写敏感，ip地址和域名允许使用通配符　　

4.UNASSIGN_ACL存储过程允许你手动删除访问控制列表，它使用的参数与ASSIGN_ACL存储过程相同，使用NULL参数作为通配符。

DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.qq.com');

5.删除上面的控制列表

DBMS_NETWORK_ACL_ADMIN.drop_acl ( acl => 'www.xml');

6. 查询创建的ACL信息

SELECT host, lower_port, upper_port, acl,

DECODE(

DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'HR', 'connect'),

1, 'GRANTED', 0, 'DENIED', NULL) privilege

FROM dba_network_acls

ORA-24247: network access denied by access control list (ACL) 错误处理
及DBMS_NETWORK_ACL_ADMIN用法汇总
通过oracle的存储过程发邮件，出现问题，具体过程如下：
发邮件的存储过程PROC_SENDMAIL_SIMPLE在A用户，而B用户要调用A用的PROC_SENDMAIL_SIMPLE来发邮件。
其中，A用户已经把PROC_SENDMAIL_SIMPLE的执行权限给了B用户
grant execute on PROC_SENDMAIL_SIMPLE to B;
但是在B用户的存储过程中调用PROC_SENDMAIL_SIMPLE依然报错
ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝
ORA-24247: network access denied by access control list (ACL) 
发生这个错误是因为网络访问控制列表管理着用户访问网络的权限。
========
解决办法：
========
拥有DBA权限的用户执行下面的SQL，分3部分
 
BEGIN
--1.创建访问控制列表sendmail.xml，sendmail.xml控制列表拥有connect权限，并把这个权限给了B用户，
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
                       acl=> 'sendmail.xml',                       -- ACL的名字，自己定义    
                       description => 'sendmail ACL',          -- ACL的描述
                       principal   => 'B',                              -- 这里是用户名，大写，表示把这个ACL的权限赋给B用户
                       is_grant    => true,                            --true：授权 ;false：禁止
                        privilege   => 'connect');                     --授予或者禁止的网络权限
--2.为sendmail.xml控制列表添加resolve权限，且赋给B用户
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
                           acl=> 'sendmail.xml',
                                       principal => 'B',
                                       is_grant  => true,
                                       privilege => 'resolve');
--3.为控制列表ACL sendmail.xml分配可以connect和resolve的host
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
                        acl  => 'sendmail.xml',
                                    host => 'smtp.163.com'); --smtp.163.com是邮箱服务器主机名
END;
/
COMMIT;
再次在用户B调用A的PROC_SENDMAIL_SIMPLE发邮件过程，成功发送邮件。
======================联想到其他情况======================
情况1：同一个ACL给多个用户使用
用户B调用A的发邮件存储过程PROC_SENDMAIL_SIMPLE，那么C用户很可能也要这么做。
这时，不必创建一个新的ACL，用原有的ACL sendmail.xml即可，也就是把sendmail.xml给用户C使用。
这样C用户自然可以访问网络发送邮件。
BEGIN
--给C用户resolve权限
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'sendmail.xml',
                                       principal => 'C',
                                       is_grant  => true,
                                       privilege => 'resolve');
--给C用户 conenct权限
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'sendmail.xml',
                                       principal => 'C',
                                       is_grant  => true,
                                       privilege => 'connect');
END;
/
COMMIT;
情况2：取消给ACL sendmail.xml 指派的主机smtp.163.com ，也就是所有使用sendmail.xml 的用户都不能connect和resolve主机smtp.163.com
<1>查看一下
select * from dba_network_acls;
HOST          LOWER_PORT  UPPER_PORT          ACL               ACLID
-----------------     ------------------     -----------------     ---------------------     --------------------------
smtp.163.com                              /sys/acls/sendmail.xml     D07B6F4707E7EFFDE040007F01005C7F
<2>收回sendmail.xml控制列表中访问smtp.163.com的权限
BEGIN
  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'smtp.163.com');
END;
/
COMMIT;
<3>
select * from dba_network_acls;
空
不过这时ACL sendmail.xml依然存在，只不过sendmail.xml中没有任何主机信息
<4>那么怎么让sendmail.xml重新能访问smtp.163.com呢？
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
                        acl  => 'sendmail.xml',
                                    host => 'smtp.163.com');
END;
/
COMMIT;
<5>再次看,sendmail.xml中含有主机smtp.163.com了
select * from dba_network_acls;
HOST               LOWER_PORT UPPER_PORT ACL                      ACLID
-------------------- ---------- ---------- ------------------------------ --------------------------------
smtp.163.com                       /sys/acls/sendmail.xml       D07B6F4707xFFDExx007F01005C7F
情况3：取消B用户使用sendmail.xml ACL，B用户不能访问smtp.163.com 主机了
BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'sendmail.xml',
        principal   => 'B')
END;
=========================================================================
================DBMS_NETWORK_ACL_ADMIN知识汇总==================
=========================================================================
说了这么多，其实都是对DBMS_NETWORK_ACL_ADMIN过程的使用。
下面是DBMS_NETWORK_ACL_ADMIN的相关只是汇总。
1.创建ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
   acl             IN VARCHAR2,
   description     IN VARCHAR2,
   principal       IN VARCHAR2,
   is_grant        IN BOOLEAN,
   privilege       IN VARCHAR2,
   start_date      IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   end_date        IN TIMESTAMP WITH TIMEZONE DEFAULT NULL );
例子：
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'WWW ACL',
                                    principal   => 'SCOTT',
                                    is_grant    => true,
                                    privilege   => 'connect');
END;
/
COMMIT;
2.为ACL添加权限
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
   acl             IN VARCHAR2,
   principal       IN VARCHAR2,
   is_grant        IN BOOLEAN,
   privilege       IN VARCHAR2,
   position        IN PLS_INTEGER DEFAULT NULL,
   start_date      IN TIMESTAMP WITH TIMESTAMP DEFAULT NULL,
   end_date        IN TIMESTAMP WITH TIMESTAMP DEFAULT NULL );
例子：
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
                          acl => 'www.xml',
                                       principal => 'SCOTT',
                                       is_grant  => true,
                                       privilege => 'resolve');
END;
/
COMMIT;
3.指派ACL可以访问的host
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
   acl         IN VARCHAR2,
   host        IN VARCHAR2,
   lower_port  IN PLS_INTEGER DEFAULT NULL,
   upper_port  IN PLS_INTEGER DEFAULT NULL);
注意：host这个参数可以写作
一个网址：www.us.oracle.com
也可以是一个网段：*.us.oracle.com或者*.oracle.com或者*.com
当然也可以是所有host：*
例子：
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl         => 'us-oracle-com-permissions.xml',
     host        => '*.us.oracle.com',
     lower_port  => 80);
END;
4.检测用户是否拥有某个ACL中的某个权限
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (
   acl             IN VARCHAR2,
   user            IN VARCHAR2,
   privilege       IN VARCHAR2)
  RETURN NUMBER;
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
例子：
如scott拥有sendmail.xml中的resolve权限
SELECT DECODE(
  DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(
       'sendmail.xml', 'SCOTT', 'resolve'),
  1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;
PRIVILE
-------
GRANTED
5.删除acl中的connect或者resolve权限
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE (
   acl           IN VARCHAR2,
   principal     IN VARCHAR2,
   is_grant      IN BOOLEAN DEFAULT NULL,
   privilege     IN VARCHAR2 DEFAULT NULL);
例子：
BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'us-oracle-com-permissions.xml',
        principal   => 'ST_USERS')
END;
6.删除ACL
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
   acl           IN VARCHAR2);
例子：
BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'us-oracle-com-permissions.xml');
END;
7.取消ACL已分配的host
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
   acl         IN VARCHAR2 DEFAULT NULL,
   host        IN VARCHAR2 DEFAULT NULL,
   lower_port  IN PLS_INTEGER DEFAULT NULL,
   upper_port  IN PLS_INTEGER DEFAULT NULL);
例子：
BEGIN
   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(
     host        => '*.us.oracle.com',
     lower_port  => 80);
END;
8.查看语句
--ACL的信息，包括host，ACL名字等。
select * from dba_network_acls;
--各用户对应的ACL，用户拥有的权限
select acl,principal,privilege,is_grant,to_char(start_date, 'dd-mon-yyyy') as start_date,to_char(end_date, 'dd-mon-yyyy') as end_date from dba_network_acl_privileges;
参考
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm

How To Install Package DBMS_NETWORK_ACL_ADMIN (文档 ID 1118447.1)

In this Document

APPLIES TO:

Oracle Server - Enterprise Edition - Version 11.2.0.1 and later

Information in this document applies to any platform.

***Checked for relevance on 24-Oct-2012***

GOAL

How to install the dbms_network_acl_admin package?

FIX

The DBMS_NETWORK_ACL_ADMIN package is installed when the XDB component is being installed. To install XDB follow the procedure outlined in - Master Note for Oracle XML Database (XDB) Installation.

If XDB is already installed but the ACL package is not available and a reinstall of XDB is not possible then the only way to install the DBMS_NETWORK_ACL_ADMIN package is to run the *nacl scripts:

     sqlplus / as sysdba  
 run ?/rdbms/admin/catnacl.sql  
 run ?/rdbms/admin/dbmsnacl.sql 
 run ?/rdbms/admin/prvtnacl.plb 

REFERENCES

- All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault

- Master Note for Oracle XML Database (XDB) Install / Deinstall

Master Note for Oracle XML Database (XDB) Install / Deinstall (文档 ID 1292089.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.3 to 12.1.0.1 [Release 9.2 to 12.1]

Oracle Multimedia - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]

Information in this document applies to any platform.

***Checked for relevance on 13-Oct-2014***

DETAILS

This master note provides information for DBA's on removing and installing XML Database (XDB). This note covers releases 9.2.0.3 through 11.2.

If XDB must be reinstalled in a database supporting Ebusiness Suite there are some actions to do on the database before removing XDB and that needs to be defined with the EBS team.

For example if iSetup exists, iSetup dependency with Deinstall and Reinstall of XMLDB (Doc ID 402785.1)

should be followed before and after the XDB reinstallation.

For an EBS database please consult with the EBS team before reinstalling XDB.

Ask Questions, Get Help, And Share Your Experiences With This Article

     Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts? 
 (  to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.) 
 Discover discussions about other articles and helpful subjects by clicking   to access the main My Oracle Support Community page for Oracle XDB. 

ACTIONS

Best Practices

Please note that 9.2.0.3 is a mandatory minimum patch level for XDB. Customers using XDB must be on Oracle database version 9.2.0.3 or higher.

If the XML DB features are currently being utilized and you have experienced an invalid XML DB repository or installation, point-in-time recovery to a point prior to when the problem occurred is recommended. If XDB is removed and reinstalled, and the feature is being used, data loss can occur. Removing XDB by running catnoqm.sql does the following:

- Deletes all information stored in the oracle XML DB repository and XDB database schema.
- Permanently invalidates any xmltype tables or columns that are associated with a registered XML schema. If the XDB user is dropped, there is no way to recover the data in these xmltype tables or columns.

Please confirm whether XDB is being used in your environment prior to attempting removal and reinstall. To accomplish this, refer to the following documents:

( ) 9iR2: How to Determine if XDB is Being Used in the Database?

( ) 10g: How to Determine if XDB is Being Used in the Database?

( ) 11g: How to Determine if XDB is Being Used in the Database?

Please note, later versions of RDA provides some details on the current status of the XDB component. Please see the following document for more information on RDA:

( ) Remote Diagnostic Agent (RDA) 4 - Getting Started

The following database components / features also use XDB:

Oracle Application Express (APEX)

Oracle Expression Filter

Oracle interMedia / Multimedia DICOM

Oracle Multimedia Image metadata extraction of EXIF, ORDIMAGE, IPTC and XMP metadata

Spatial (including Oracle Locator)

OLAP

Oracle Applications such as iRecruitment

Any procedure using UTL_TCP, UTL_HTTP, UTL_SMTP

XMLTYPE operations

XBRL (Extensible Business Reporting Language) from 11.2.0.2 onwards

It is recommended to take a full backup of the database before removing / reinstalling XDB. This is a good precautionary measure for situations where it is needed to go back to the original state of the database. Please see the following document for more information:

( ) How To Backup XML Database (XDB)

Be sure to follow the exact steps listed whenever reloading the XDB component or reinstalling XDB. This includes a startup/shutdown of the database, as failure to do so can cause the XDB installation to fail with an internal error similar to the following: ORA-7445 [qmr_hdl_copy()+48].

AL32UTF8 is the recommended characterset for XML Database (XDB). AL32UTF8 supports all valid characters for XDB. You should only use a non-AL32UTF8 characterset if you can guarantee that all your XDB data is part of the characterset. If you aren't using AL32UTF8, it is typically recommended to convert to the database character set AL32UTF8. If you are not using AL32UTF8, there could be possible character losses or characterset conversion errors.

RAC procedures.
- If you are reloading XDB on an RAC cluster, all nodes must be shutdown and restarted to avoid inconsistent memory structures.
- Please note, if running xdbrelod.sql for a RAC environment to avoid errors ORA-1092 and ORA-39701:

- - Shutdown database database cleanly
  - The "cluster_database" parameter should be set to value "false" so for init.ora parameter file set cluster_database=false
  - Start only one instance and execute script for XDB reload
  - Revert cluster_database=true and restart database

Always review the compatibility parameter to ensure that it is set to the same version as the database so that all XDB functionality associated with that database version is available.

Before installing or upgrading XDB, make sure the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable is set correctly. That is, the first directory referenced should be $ORACLE_HOME/lib. This environment variable is used to resolve the location of the shared library "libxdb.so (libxdb.sl on HP)".

XDB must have execute permissions on the DBMS_LOB, UTL_FILE, DBMS_JOB, UTL_RAW and DBMS_SQL packages. XDB automatically has these privileges because they are granted to PUBLIC by default. If these privileges have been revoked from PUBLIC for security reasons, errors will be reported during the installation / upgrade of XDB and some XDB objects will become invalid, making the component itself invalid. Therefore, it is recommended to grant these privileges back to PUBLIC before installing /upgrading XDB. Then after the install/upgrade, grant execute permissions on these packages directly to XDB and revoke the privileges from PUBLIC. After a successful installation of XDB, execute the following:

connect / as sysdba

grant execute on DBMS_LOB to XDB;

grant execute on UTL_FILE to XDB;

grant execute on DBMS_SQL to XDB;

grant execute on DBMS_JOB to XDB;

grant execute on DBMS_STATS to XDB;

grant execute on UTL_RAW to XDB;

revoke execute on DBMS_LOB from PUBLIC;

revoke execute on UTL_FILE from PUBLIC;

revoke execute on DBMS_SQL from PUBLIC;

revoke execute on DBMS_JOB from PUBLIC;

revoke execute on UTL_RAW from PUBLIC;

Please be sure to review the note listed below whenever revoking execute permissions from PUBLIC:

() Be Cautious When Revoking Privileges Granted to PUBLIC

Make sure you do not have a table named XDB or any synonyms pointing to XDB objects. Otherwise, this will lead to errors such as ORA-01422: exact fetch returns more than requested number of rows. Please see the following documents for more information:

() ORA-01422 from DBMS_XS_PRINCIPAL_EVENTS_INT DBA|ALL|USER_XSC_* and DBA|ALL|USER_XDS_*

() Selecting from SYS.RESOURCE_VIEW Fails with ORA-01422 and selecting from SYS.DBA_NETWORK_ACLS Fails with ORA-600 [qmxqtmChkXQAtomMapSQL:2]

XDB is included with the Oracle RDBMS. It is not licensed separately.

Reloading XDB

The reload procedure recreates all of the PL/SQL packages and types. It can be helpful in addressing an INVALID status of XDB in DBA_REGISTRY, invalid XDB-specific objects, etc. An XDB reload is always preferred over an XDB removal and reinstall. Since xdbrelod.sql is called in xdbpatch.sql, you can alternatively run xdbpatch.sql to recreate all of the XDB related packages.

Oracle 9i - XDB Reload

spool xdbreload.log

connect / as sysdba

set echo on;

shutdown immediate;

startup migrate;

@?/rdbms/admin/xdbrelod.sql

shutdown immediate;

startup;

@?/rdbms/admin/utlrp.sql

spool off

Oracle 10.1 and above - XDB Reload

spool xdbreload.log

connect / as sysdba

set echo on;

shutdown immediate;

startup upgrade;

@?/rdbms/admin/xdbrelod.sql

shutdown immediate;

startup;

@?/rdbms/admin/utlrp.sql

spool off

Deinstalling and Reinstalling XDB

Prior to upgrading a database with XML Database (XDB) installed or installing XDB, be sure to run the following code listed below to determine if any objects need to be dropped. Please note, failure to run the code listed below could result in data loss of user objects like tables, indexes. This is fully documented in:

() Upgrading or Installing XDB could result in data loss if XDB_INSTALLATION_TRIGGER exists

     connect  / as sysdba 
 set serveroutput on 
 DECLARE 
     v_xdb_installation_trigger number; 
     v_dropped_xdb_instll_trigger number; 
     v_dropped_xdb_instll_tab number; 
 BEGIN 
     select count(*) into v_xdb_installation_trigger 
     from dba_triggers 
     where trigger_name = 'XDB_INSTALLATION_TRIGGER' and owner = 'SYS'; 
     select count(*) into v_dropped_xdb_instll_trigger 
     from dba_triggers 
     where trigger_name = 'DROPPED_XDB_TRIGGER' and owner = 'SYS'; 
     select count(*) into V_dropped_xdb_instll_tab 
     from dba_tables 
     where table_name = 'DROPPED_XDB_INSTLL_TAB' and owner = 'SYS'; 
   IF v_xdb_installation_trigger > 0 OR v_dropped_xdb_instll_trigger > 0 OR v_dropped_xdb_instll_tab > 0 then 
   IF v_xdb_installation_trigger > 0 THEN 
 dbms_output.put_line('Please proceed to run the command SQL> drop trigger sys.xdb_installation_trigger'); 
 --   drop trigger sys.xdb_installation_trigger; 
   END IF; 
   IF v_dropped_xdb_instll_trigger > 0 THEN 
    dbms_output.put_line('Please proceed to run the command SQL> drop trigger sys.dropped_xdb_instll_trigger'); 
 --   drop trigger sys.dropped_xdb_instll_trigger; 
   END IF; 
   IF v_dropped_xdb_instll_tab > 0 THEN 
    dbms_output.put_line('Please proceed to run the command SQL> drop table sys.dropped_xdb_instll_tab'); 
 --   drop table sys.dropped_xdb_instll_tab; 
   END IF; 
    ELSE 
    dbms_output.put_line('Please proceed to run the XDB install or upgrade'); 
   END IF; 
 END; 
 / 

Use XDB removal and reinstall only if not using this feature or under the direction of Oracle Support after it has been verified which objects will need to be recreated.

For database releases 10.1.x and above, XDB is mandatory in order to use any of the XMLTYPE functions. This is true even if the XDB repository is not being used and/or there are no registered schemas.

Prior to Oracle 11.1, a valid installation of JAVA Virtual Machine (JVM) is required.

Prior to Oracle 10.2, a valid installation of XDK is also required.

Allocate at least 350 MB or enable the autoextend on the datafile for the XDB repository tablespace datafile. To determine if the XDB tablespace has the necessary space to run the XDB installation, execute the following PL/SQL procedure:

>







>