这两天在测试使用UTL_SMTP发送邮件时，遇到了ORA－24247的错误：
Error report:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at line 9
我使用的数据库是11gR2，在11g的安全控制得到了进一步的提升，在使用工具包进行tcp连接时，Oracle数据库11g做了较为严格的控制。
Oracle 数据库 11g有一个新的解决方案：您可以将执行权限程序包授予任何人，但要控制他们可以调用的资源。例如，utl_tcp 可限制为仅调用几个 IP地址，这种机制称为访问控制列表 (ACL)。如果主机在 ACL 中，用户可以在 utl_tcp 中使用；但是仅仅拥有对 utl_tcp的执行权限是不够的。因此，恶意进程不可能取代 utl_tcp 程序包和建立非法连接。
那些所熟悉的 工具包有UTL_TCP、UTL_HTTP 和 UTL_SMTP等，它们支持数据库领域外的服务器之间的通信。例如，utl_tcp 用于在两台主机间建立 TCP/IP通信，而不是通过数据库链接。类似地，utl_http 用于从 Web 服务器发出 http 请求，utl_smtp 用于在主机间进行 SMTP邮件呼叫。
先看看两个使用acl控制tcp访问的例子：
普通用户，暂时未授予acl访问权限的用户，使用utl_http进行web请求时会出现ORA－24247的错误：
11:29:03 osdbso@SRCB> select utl_http.request('http://www.baidu.com') from dual;
select utl_http.request('http://www.baidu.com') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
超级用户，或者是已授予了acl的用户，则可以使用utl_http进行web请求：
11:29:10 osdbso@SYS> select utl_http.request('http://www.baidu.com') from dual;
UTL_HTTP.REQUEST('HTTP://WWW.BAIDU.COM')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<!doctype html><html><head><meta http-equiv="Content-Type" content="text/html;charset=gb2312"><title>鐧惧害涓�涓嬶紝浣犲氨鐭ラ亾 </title><style>html{overflow-y:auto}body{font:12px arial;text-align:center;
background:#fff}body,p,form,ul,li{margin:0;padding:0;list-style:none}body,form,#fm{position:relative}td{text-align:left}img{border:0}a{color:#00c}a:active{color:#f60}#u{color:#999;padding:4px 10px 5px
0;text-align:right}#u a{margin:0 5px}#u .reg{margin:0}#m{width:720px;margin:0 auto;}#nv a,#nv b,.btn,#lk{font-size:14px}#fm{padding-left:110px;text-align:left}input{border:0;padding:0}#nv{height:19px
;font-size:16px;margin:0 0 4px;text-align:left;text-indent:137px;}.s_ipt_wr{width:418px;height:30px;display:inline-block;margin-right:5px;background:url(http://s1.bdstatic.com/r/www/img/i-1.0.0.png) n
o-repeat -304px 0;border:1px solid #b6b6b6;border-color:#9a9a9a #cdcdcd #cdcdcd #9a9a9a;vertical-align:top}.s_ipt{width:405px;height:22px;font:16px/22px arial;margin:5px 0 0 7px;background:#fff;outlin
e:none;-webkit-appearance:none}.s_btn{width:95px;height:32px;padding-top:2px\9;font-size:14px;background:#ddd url(http://s1.bdstatic.com/r/www/img/i-1.0.0.png);cursor:pointer}.s_btn_h{background-posit
ion:-100px 0}.s_btn_wr{width:97px;height:34px;display:inline-block;background:url(http://s1.bdstatic.com/r/www/img/i-1.0.0.png) no-repeat -202px 0;*position:relative;z-index:0;vertical-align:top}#lg i
mg{vertical-align:top;margin-bottom:3px}#lk{margin:33px 0}#lk span{font:14px "瀹嬩綋"}#lm{height:60px}#lh{margin:16px 0 5px;word-spacing:3px}.tools{position:absolute;top:-4px;*top:10px;right:7px;}#mHold
er{width:62px;position:relative;z-index:296;display:none}#mCon{height:18px;line-height:18px;position:absolute;cursor:pointer;padding:0 18px 0 0;background:url(http://s1.bdstatic.com/r/www/img/bg-1.0.0
.gif) no-repeat right -134px;background-position:right -136px\9}#mCon span{color:#00c;cursor:default;display:block}#mCon .hw{text-decoration:underline;cursor:pointer}#mMenu a{width:100%;h
接下来看看acl访问控制的工作原理，以及实现步骤：
1、使用dbms_network_acl_admin包创建一个ACL：
11:38:18 osdbso@SYS> begindbms_network_acl_admin.create_acl (
acl => 'myutltcpacl.xml',
description => 'Normal Access',
principal => 'CONNECT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
PL/SQL procedure successfully completed.
注明此处，参数 principal => 'CONNECT' 表示该 ACL 适用于 CONNECT 角色。您可以在此处定义一个用户或角色。该 ACL 是作为一个 myutltcpacl.xml 文件创建的。还可以指定start_date和end_data两个参数来限定acl的有效期。
创建完毕后，您可以进行检查以确保该 ACL 已增加：
ANY_PATH
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sys/acls/myutltcpacl.xml
2、把acl权限授予需要的用户：
11:45:16 osdbso@SYS> begin
dbms_network_acl_admin.add_privilege (
acl => 'myutltcpacl.xml',
principal => 'SRCB',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
end;
/
PL/SQL procedure successfully completed.
其中，acl对应的参数myutltcpac.xml即为上一步中创建的acl权限文件，授予用户为SRCB，同样可以使用start_data和end_date来现在用户的acl访问期限。
3、设定acl限制的主机及其他信息：
11:50:36 osdbso@SYS> begin
dbms_network_acl_admin.assign_acl (
acl => 'myutltcpacl.xml',
host => 'www.baidu.com',
lower_port => 22,
upper_port => 55);
end;
/
PL/SQL procedure successfully completed.
本实例中表示，限定用户SRCB只能访问主机www.baidu.com，且只能访问www.baidu.com的22到55端口。如果此时想访问www.baidu.com的80端口则依然会出现ORA－24247的错误：
11:54:29 osdbso@SRCB> select utl_http.request('http://www.baidu.com') from dual;
select utl_http.request('http://www.baidu.com') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
因此，我们要根据实际中的需要限定正确的范围，如：
11:56:17 osdbso@SYS> begin
dbms_network_acl_admin.assign_acl (
acl => 'myutltcpacl.xml',
host => 'www.baidu.com',
lower_port => 20,
upper_port => 128);
end;
/
PL/SQL procedure successfully completed.
现在我们使用SRCB用户就可以正常访问www.baidu.com的80端口了：
12:00:02 osdbso@SRCB> select utl_http.request('www.baidu.com') from dual;
UTL_HTTP.REQUEST('WWW.BAIDU.COM')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<!doctype html><html><head><meta http-equiv="Content-Type" content="text/html;charset=gb2312"><title>鐧惧害涓�涓嬶紝浣犲氨鐭ラ亾 </title><style>html{overflow-y:auto}body{font:12px arial;text-align:center;
background:#fff}body,p,form,ul,li{margin:0;padding:0;list-style:none}body,form,#fm{position:relative}td{text-align:left}img{border:0}a{color:#00c}a:active{color:#f60}#u{color:#999;padding:4px 10px 5px
0;text-align:right}#u a{margin:0 5px}#u .reg{margin:0}#m{width:720px;margin:0 auto;}#nv a,#nv b,.btn,#lk{font-size:14px}#fm{padding-left:110px;text-align:left}input{border:0;padding:0}#nv{height:19px
;font-size:16px;margin:0 0 4px;text-align:left;text-indent:137px;}.s_ipt_wr{width:418px;height:30px;display:inline-block;margin-right:5px;background:url(http://s1.bdstatic.com/r/www/img/i-1.0.0.png) n
o-repeat -304px 0;border:1px solid #b6b6b6;border-color:#9a9a9a #cdcdcd #cdcdcd #9a9a9a;vertical-align:top}.s_ipt{width:405px;height:22px;font:16px/22px arial;margin:5px 0 0 7px;background:#fff;outlin
e:none;-webkit-appearance:none}.s_btn{width:95px;height:32px;padding-top:2px\9;font-size:14px;background:#ddd url(http://s1.bdstatic.com/r/www/img/i-1.0.0.png);cursor:pointer}.s_btn_h{background-posit
ion:-100px 0}.s_btn_wr{width:97px;height:34px;display:inline-block;background:url(http://s1.bdstatic.com/r/www/img/i-1.0.0.png) no-repeat -202px 0;*position:relative;z-index:0;vertical-align:top}#lg i
mg{vertical-align:top;margin-bottom:3px}#lk{margin:33px 0}#lk span{font:14px "瀹嬩綋"}#lm{height:60px}#lh{margin:16px 0 5px;word-spacing:3px}.tools{position:absolute;top:-4px;*top:10px;right:7px;}#mHold
er{width:62px;position:relative;z-index:296;display:none}#mCon{height:18px;line-height:18px;position:absolute;cursor:pointer;padding:0 18px 0 0;background:url(http://s1.bdstatic.com/r/www/img/bg-1.0.0
.gif) no-repeat right -134px;background-position:right -136px\9}#mCon span{color:#00c;cursor:default;display:block}#mCon .hw{text-decoration:underline;cursor:pointer}#mMenu a{width:100%;h
该规则仅适用于 www.baidu.com。如果您调用其他 Web 站点，调用将失败，错误原因仍为ORA-24247。这是最细粒度水平上的安全性。如果您的企业需要连接到主机www.baidu.com，您可以在允许该连接的同时阻止对任何其他主机的访问，从而防止恶意用户使用该功能来访问所有其他的主机。
要了解 ACL 的详细信息，请查询 DBA_NETWORK_ACLS 视图：
17:25:41 osdbso@SYS> select host, lower_port, upper_port, acl, aclid from dba_network_acls where ACL='/sys/acls/myutltcpacl.xml';
HOST
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOWER_PORT UPPER_PORT
---------- ----------
ACL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACLID
--------------------------------
www.baidu.com
22 55
/sys/acls/myutltcpacl.xml
CB11F58530D41CDCE0430164A8C07839
www.baidu.com
20 128
/sys/acls/myutltcpacl.xml
CB11F58530D41CDCE0430164A8C07839
最后附上dbms_network_acl_admin包的说明：
create or replace
package dbms_network_acl_admin is
/*
* DBMS_NETWORK_ACL_ADMIN is the PL/SQL package that provides the interface
* to administer the network ACL. The EXECUTE privilege on the package will
* be granted only to the DBA role by default.
*/
----------------
-- Exceptions --
----------------
ace_already_exists EXCEPTION;
empty_acl EXCEPTION;
acl_not_found EXCEPTION;
invalid_acl_path EXCEPTION;
invalid_host EXCEPTION;
invalid_privilege EXCEPTION;
invalid_wallet_path EXCEPTION;
bad_argument EXCEPTION;
PRAGMA EXCEPTION_INIT(ace_already_exists, -24243);
PRAGMA EXCEPTION_INIT(empty_acl, -24246);
PRAGMA EXCEPTION_INIT(acl_not_found, -31001);
PRAGMA EXCEPTION_INIT(invalid_acl_path, -46059);
PRAGMA EXCEPTION_INIT(invalid_host, -24244);
PRAGMA EXCEPTION_INIT(invalid_privilege, -24245);
PRAGMA EXCEPTION_INIT(invalid_wallet_path, -29248);
PRAGMA EXCEPTION_INIT(bad_argument, -29261);
ace_already_exists_num constant PLS_INTEGER := -24243;
empty_acl_num constant PLS_INTEGER := -24246;
acl_not_found_num constant PLS_INTEGER := -31001;
invalid_acl_path_num constant PLS_INTEGER := -46059;
invalid_host_num constant PLS_INTEGER := -24244;
invalid_privilege_num constant PLS_INTEGER := -24245;
invalid_wallet_path_num constant PLS_INTEGER := -29248;
bad_argument_num constant PLS_INTEGER := -29261;
-- IP address mask: xxx.xxx.xxx.xxx
IP_ADDR_MASK constant VARCHAR2(80) := '([[:digit:]]+\.){3}[[:digit:]]+';
-- IP submet mask: xxx.xxx...*
IP_SUBNET_MASK constant VARCHAR2(80) := '([[:digit:]]+\.){0,3}\*';
-- Hostname mask: ???.???.???...???
HOSTNAME_MASK constant VARCHAR2(80) := '[^\.\:\/\*]+(\.[^\.\:\/\*]+)*';
-- Hostname mask: *.???.???...???
DOMAIN_MASK constant VARCHAR2(80) := '\*(\.[^\.\:\/\*]+)*';
/*--------------- API for ACL and privilege administration ---------------*/
/*
* Creates an access control list (ACL) with an initial privilege setting.
* An ACL must have at least one privilege setting. The ACL has no access
* control effect unless it is assigned to a network host.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls".
* description the description attribute in the ACL
* principal the principal (database user or role) whom the privilege
* is granted to or denied from
* is_grant is the privilege is granted or denied
* privilege the network privilege to be granted or denied
* start_date the start date of the access control entry (ACE). When
* specified, the ACE will be valid only on and after the
* specified date.
* end_date the end date of the access control entry (ACE). When
* specified, the ACE will expire after the specified date.
* The end_date must be greater than or equal to the
* start_date.
* RETURN
* None
* EXCEPTIONS
*
* NOTES
* To remove the ACL, use DROP_ACL. To assign the ACL to a network host,
* use ASSIGN_ACL.
*/
procedure create_acl(acl in varchar2,
description in varchar2,
principal in varchar2,
is_grant in boolean,
privilege in varchar2,
start_date in timestamp with time zone default null,
end_date in timestamp with time zone default null);
/*
* Adds a privilege to grant or deny the network access to the user. The
* access control entry (ACE) will be created if it does not exist.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls".
* principal the principal (database user or role) whom the privilege
* is granted to or denied from
* is_grant is the privilege is granted or denied
* privilege the network privilege to be granted or denied
* position the position of the ACE. If a non-null value is given,
* the privilege will be added in a new ACE at the given
* position and there should not be another ACE for the
* principal with the same is_grant (grant or deny). If a null
* value is given, the privilege will be added to the ACE
* matching the principal and the is_grant if one exists, or
* to the end of the ACL if the matching ACE does not exist.
* start_date the start date of the access control entry (ACE). When
* specified, the ACE will be valid only on and after the
* specified date. The start_date will be ignored if the
* privilege is added to an existing ACE.
* end_date the end date of the access control entry (ACE). When
* specified, the ACE will expire after the specified date.
* The end_date must be greater than or equal to the
* start_date. The end_date will be ignored if the
* privilege is added to an existing ACE.
* RETURN
* None
* EXCEPTIONS
*
* NOTES
* To remove the privilege, use DELETE_privilege.
*/
procedure 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 time zone default null,
end_date in timestamp with time zone default null);
/*
* Delete a privilege.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls".
* principal the principal (database user or role) for whom the
* privileges will be deleted
* is_grant is the privilege is granted or denied. If a null
* value is given, the deletion is applicable to both
* granted or denied privileges.
* privilege the privilege to be deleted. If a null value is given,
* the deletion is applicable to all privileges.
* RETURN
* None
* EXCEPTIONS
*
* NOTES
* Any ACE that does not contain any privilege after the deletion will
* be removed also.
*/
procedure delete_privilege(acl in varchar2,
principal in varchar2,
is_grant in boolean default null,
privilege in varchar2 default null);
/*
* Drops an access control list (ACL).
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls".
* RETURN
* None
* EXCEPTIONS
*
*/
procedure drop_acl(acl in varchar2);
/*--------- API for ACL assignment to network hosts and wallets ---------*/
/*
* Assigns an access control list (ACL) to a network host, and optionally
* specific to a TCP port range.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls".
* host the host to which the ACL will be assigned. The host can be
* the name or the IP address of the host. A wildcard can be
* used to specify a domain or a IP subnet. The host or
* domain name is case-insensitive.
* lower_port the lower bound of a TCP port range if not NULL.
* upper_port the upper bound of a TCP port range. If NULL,
* lower_port is assumed.
* RETURN
* None
* EXCEPTIONS
*
* NOTES
* 1. The ACL assigned to a domain takes a lower precedence than the other
* ACLs assigned sub-domains, which take a lower precedence than the ACLs
* assigned to the individual hosts. So for a given host say
* "www.us.mycompany.com", the following domains are listed in decreasing
* precedences:
* - www.us.mycompany.com
* - *.us.mycompany.com
* - *.mycompany.com
* - *.com
* - *
* In the same way, the ACL assigned to an subnet takes a lower
* precedence than the other ACLs assigned smaller subnets, which take a
* lower precedence than the ACLs assigned to the individual IP addresses.
* So for a given IP address say "192.168.0.100", the following subnets
* are listed in decreasing precedences:
* - 192.168.0.100
* - 192.168.0.*
* - 192.168.*
* - 192.*
* - *
* 2. The port range is applicable only to the "connect" privilege
* assignments in the ACL. The "resolve" privilege assignments in an ACL
* have effects only when the ACL is assigned to a host without a port
* range.
* 3. For the "connect" privilege assignments, an ACL assigned to the host
* without a port range takes a lower precedence than other ACLs assigned
* to the same host with a port range.
* 4. When specifying a TCP port range, both lower_port and upper_port must
* not be NULL and upper_port must be greater than or equal to lower_port.
* The port range must not overlap with any other port ranges for the same
* host assigned already.
* 5. To remove the assignment, use UNASSIGN_ACL.
*/
procedure assign_acl(acl in varchar2,
host in varchar2,
lower_port in pls_integer default null,
upper_port in pls_integer default null);
/*
* Unassign the access control list (ACL) currently assigned to a network
* host.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls". If acl is NULL, any ACL assigned to the host
* will be unassigned.
* host the host remove the ACL assignment from. The host can be
* the name or the IP address of the host. A wildcard can be
* used to specify a domain or a IP subnet. The host or
* domain name is case-insensitive. If host is null, the ACL
* will be unassigned from any host. If both host and acl are
* NULL, all ACLs assigned to any hosts will be unassigned.
* lower_port if not NULL, the lower bound of a TCP port range for the
* host.
* upper_port the upper bound of a TCP port range. If NULL,
* lower_port is assumed.
* RETURN
* None
* EXCEPTIONS
*
*/
procedure 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);
/*
* Assigns an access control list (ACL) to a wallet.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls".
* wallet_path the directory path of the wallet to which the ACL will be
* assigned. The path is case-sensitive and of the format
* "file:<directory-path>".
* RETURN
* None
* EXCEPTIONS
*
* NOTES
* To remove the assignment, use UNASSIGN_WALLET_ACL.
*/
procedure assign_wallet_acl(acl in varchar2,
wallet_path in varchar2);
/*
* Unassign the access control list (ACL) currently assigned to a wallet.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls". If acl is NULL, any ACL assigned to the wallet
* will be unassigned.
* wallet_path the directory path of the wallet to which the ACL will be
* assigned. The path is case-sensitive and of the format
* "file:<directory-path>". If wallet_path is null, the ACL
* will be unassigned from any wallet.
* RETURN
* None
* EXCEPTIONS
*
*/
procedure unassign_wallet_acl(acl in varchar2 default null,
wallet_path in varchar2 default null);
/*
* Check if a privilege is granted to or denied from the user in an
* access control list.
*
* PARAMETERS
* acl the name of the ACL. Relative path will be relative to
* "/sys/acls".
* aclid the object ID of the ACL.
* user the user to check against. If the user is NULL, the invoker
* is assumed. The username is case-sensitive as in the
* USERNAME column of the ALL_USERS view.
* privilege the network privilege to check
* RETURN
* 1 when the privilege is granted; 0 when the privilege is denied;
* NULL when the privilege is neither granted or denied.
* EXCEPTIONS
*
*/
function check_privilege(acl in varchar2,
user in varchar2,
privilege in varchar2) return number;
function check_privilege_aclid(aclid in raw,
user in varchar2,
privilege in varchar2) return number;
/*
* This procedure is for internal use. It is the pre-delete XDB event
* handler to remove the ACL assignments when the ACL is dropped.
*/
procedure handlePreDelete(event in DBMS_XEvent.XDBRepositoryEvent);
end;
682
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
