[版主救命啊]acl添加后仍然不能使用

[叶吥落]

各位大神：

我的oralce为 11g的版本，我想获取server的ip或者hostname,执行如下语句
SELECT utl_inaddr.get_host_address FROM dual; //获取IP
SELECT utl_inaddr.get_host_name FROM dual; //获取host 名字
如果我使用dba账户，可以获取到值，没有问题。

但是如果我使用的是新建的一个非dba的账户，grant以下的权限：
grant connect, resource to test;
grant select any table to test;
grant select any dictionary to test with admin option;
grant unlimited tablespace to test;
结果还是取不到值。

我看了网上的http://blog.csdn.net/weiwenhp/article/details/9333405的帖子，什么细粒度访问网络服务，需要添加ACL来控制，于是我使用sys账户给他授权：

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'test_acl.xml',
description => 'ACL list',
principal => 'test',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'test_acl.xml',
principal => 'test',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'test_acl.xml',
host => '我机器的IP');
END;

授权命令执行成功。但是获取IP和hostname还是不行。
此时，我使用

SELECT *
FROM resource_view WHERE any_path like '/sys/acls/%.xml';

查询的结果是

那个<value error>是什么意思？执行失败了吗？
使用

SELECT * FROM dba_network_acls;

查询的结果是

使用

SELECT *  FROM dba_network_acl_privileges;

执行的结果是


我在网上找了很久，别人这样做都行，我这到底怎么了呢？ACL添加的不对还是怎么？

看到有人说添加ACL后需要重启，这个我也试过了，oracle服务器，我本地PC机都重启了，还是不能查询IP和hostname啊
，错误提示一直都是这个：


版主在不在？救命啊！！！

急急急！！！

[叶吥落]

有没有高手在啊？ 版主在不在啊？ 救命啊！！！！

[小灰狼W]

ACL我没用过 11g这一方面的权限控制的确比较变态。给你官方的解决方法参考一下吧

[小灰狼W]

1- To get the users owning UTL_% dependent objects Schema with dependent objects: SQL> SELECT DISTINCT owner FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); Schema's using interMedia and may have dependent objects: SQL> SELECT DISTINCT owner FROM all_tab_columns WHERE data_type IN ('ORDIMAGE', 'ORDAUDIO', 'ORDVIDEO', 'ORDDOC','ORDSOURCE', 'ORDDICOM') AND (data_type_owner = 'ORDSYS' OR data_type_owner = owner) AND (owner != 'PM'); 2- Connect as the user from the above result using SQL*Plus. (for Instance, Connect as Scott) 3- Executing the code with UTL Package will error ORA-24247 SQL> set serveroutput on SQL> DECLARE l_url varchar2(32767); l_conn utl_http.req; BEGIN l_url := 'http://www.oracle.com'; l_conn := utl_http.begin_request(url => l_url, method => 'POST', http_version=> 'HTTP/1.0'); dbms_output.put_line('Anonymous Block Executed Successfully'); END; / declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-6512: at "SYS.UTL_HTTP", line 1029 ORA-24247: network access denied by access control list (ACL) ORA-6512: at line 6 SQL> select utl_inaddr.get_host_address('www.oracle.com') from dual; select utl_inaddr.get_host_address('www.oracle.com') from dual * ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_INADDR", line 19 ORA-06512: at "SYS.UTL_INADDR", line 40 ORA-06512: at line 1 4- Connect as SYS as sysdba 5- Execute the below anonymous block to give access to scott. Here the Privilege has to be 'connect' for UTL_HTTP package and 'resolve' for UTL_INADDR Package. Anonymous Block give Connect Privilege to Scott: SQL> set serveroutput on SQL> DECLARE ACL_PATH VARCHAR2(32767); BEGIN -- Look for the ACL currently assigned to '*' and give SCOTT -- the "connect" privilege if SCOTT does not have the privilege yet SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL; dbms_output.put_line('acl_path = '|| acl_path); dbms_output.put_line('ACL already Exists. Checks for Privilege and add the Privilege'); IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH,'SCOTT','connect') IS NULL THEN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'SCOTT', TRUE, 'connect'); COMMIT; END IF; EXCEPTION -- When no ACL has been assigned to '*' WHEN NO_DATA_FOUND THEN dbms_output.put_line('SCOTT does not have privilege, create ACL now'); DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('users.xml', 'ACL that lets SCOTT to use the UTL Package', 'SCOTT', TRUE, 'connect'); dbms_output.put_line('SCOTT does not have privilege, assign ACL now'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('users.xml','www.oracle.com'); COMMIT; END; / SCOTT does not have privilege, create ACL now SCOTT does not have privilege, assign ACL now PL/SQL procedure successfully completed. Note: It is important to note that the string for the host specified while assigning the ACL must be the same as the string used while calling UTL_INADDR. If for example UTL_INADDR is using an IP address or a network alias for www.oracle.com then defining an ACL for "www.oracle.com" is not going to solve the problem. Anonymous Block give Resolve Privilege to Scott: SQL> set serveroutput on SQL> DECLARE ACL_PATH VARCHAR2(32767); BEGIN -- Look for the ACL currently assigned to '*' and give SCOTT -- the "resolve" privilege if SCOTT does not have the privilege yet SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL; dbms_output.put_line('acl_path = '|| acl_path); dbms_output.put_line('ACL already Exists. Checks for Privilege and add the Privilege'); IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH,'SCOTT','resolve') IS NULL THEN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'SCOTT', TRUE, 'resolve'); COMMIT; END IF; EXCEPTION -- When no ACL has been assigned to '*' WHEN NO_DATA_FOUND THEN dbms_output.put_line('SCOTT does not have privilege, create ACL now'); DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('users.xml', 'ACL that lets SCOTT to use the UTL Package', 'SCOTT', TRUE, 'resolve'); dbms_output.put_line('SCOTT does not have privilege, assign ACL now'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('users.xml','www.oracle.com'); COMMIT; END; / acl_path = /sys/acls/users.xml ACL already Exists. Checks for Privilege and add the Privilege PL/SQL procedure successfully completed. 6- Conn as scott/tiger. Anonymous Block for UTL_HTTP: SQL> set serveroutput on SQL> DECLARE l_url varchar2(32767); l_conn utl_http.req; BEGIN l_url := 'http://www.oracle.com'; l_conn := utl_http.begin_request(url => l_url, method => 'POST', http_version=> 'HTTP/1.0'); dbms_output.put_line('Anonymous Block Executed successfully'); END; / Anonymous Block Executed successfully PL/SQL procedure successfully completed. SELECT statement for UTL_INADDR; SQL> select utl_inaddr.get_host_address('www.oracle.com') IPADDR from dual; IPADDR --------------------------------------------------------------------------- 64.233.189.104 1 row selected.

[一生一事]

SELECT  DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'test', 'connect')
FROM dba_network_acls;

看看返回啥

[叶吥落]

引用 3 楼 wildwave 的回复:

1- To get the users owning UTL_% dependent objects 1 row selected.

版主： 我按照第5步那样做了，可是还是不行，还是以前的那个提示。还有没有其他的招啊？ 另外我有两点疑问： 1.第五步中原文使用了*和www.oracle.com，我具体情况是： oracle数据库ip192.168.1.2 本机ip192.168.1.3 我应该如何替换呢？ 全部使用本地ip1.3，不行；全部使用oracle数据库ip1.2，不行；然后我把*替换成本机ip1.3，www.oracle.com替换成数据库ip1.2.。还是不行，我哭。。。。 2.第五步的注意中有： It is important to note that the string for the host specified while assigning the ACL must be the same as the string used while calling UTL_INADDR. If for example UTL_INADDR is using an IP address or a network alias for www.oracle.com then defining an ACL for "www.oracle.com" is not going to solve the problem. 是否是说acl中ip和我本机应该相同？我想获取服务器的ip使用的命令是

SELECT utl_inaddr.get_host_address FROM dual;  

我根本没有使用字符串啊，何来使用的字符串一说？

[叶吥落]

引用 4 楼 zuorxk_wl 的回复:

SELECT  DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'test', 'connect')
FROM dba_network_acls;

看看返回啥

返回

[一生一事]

返回1的话说明你的用户名是成功添加到ACL中的，那么错误就出在给网络分配ACL上 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'test_acl.xml', host => '我机器的IP'); 这里的话基本就是ip地址不对了。

[叶吥落]

引用 7 楼 zuorxk_wl 的回复:

返回1的话说明你的用户名是成功添加到ACL中的，那么错误就出在给网络分配ACL上 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'test_acl.xml', host => '我机器的IP'); 这里的话基本就是ip地址不对了。

ip地址不对？什么意思？ 我换成我的计算机名试过了，还是不行啊。 使用oracle数据库所在的机器ip也不行。

[小灰狼W]

UTL_INADDR.GET_HOST_ADDRESS函数的本意就将网络中的主机名转换成ip地址 要添加到acl list中的host应该是数据库所在的主机 由于支持通配符，用host=>'*'试试

[叶吥落]

引用 9 楼 wildwave 的回复:

UTL_INADDR.GET_HOST_ADDRESS函数的本意就将网络中的主机名转换成ip地址 要添加到acl list中的host应该是数据库所在的主机 由于支持通配符，用host=>'*'试试

搞定了，太感谢了，实在感谢。