1- To get the users owning UTL_% dependent objectsSchema 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-24247SQL> 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 6SQL> 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 nowPL/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 PrivilegePL/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 successfullyPL/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.1041 row selected.
SELECT DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'test', 'connect') FROM dba_network_acls;看看返回啥
版主: 我按照第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; 我根本没有使用字符串啊,何来使用的字符串一说?
11g这一方面的权限控制的确比较变态。给你官方的解决方法参考一下吧
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-24247SQL> 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 6SQL> 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 nowPL/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 PrivilegePL/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 successfullyPL/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.1041 row selected.
SELECT DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'test', 'connect')
FROM dba_network_acls;看看返回啥
版主:
我按照第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;
我根本没有使用字符串啊,何来使用的字符串一说?
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'test_acl.xml',
host => '我机器的IP');
这里的话基本就是ip地址不对了。
ip地址不对?什么意思?我换成我的计算机名试过了,还是不行啊。
使用oracle数据库所在的机器ip也不行。
要添加到acl list中的host应该是数据库所在的主机
由于支持通配符,用host=>'*'试试