本帖最后由 wdnmg 于 2011-12-4 21:01 編輯

我需要在數據庫中的TEST用戶中使用utl_inaddr，執行select語句出現錯誤：

SQL> select UTL_INADDR.GET_HOST_ADDRESS DST_IP from dual;

select UTL_INADDR.GET_HOST_ADDRESS DST_IP 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

然后給test用戶進行ACL創建及賦權

(1)Create ACL and privileges

begin

dbms_network_acl_admin.create_acl (

acl => 'UTL_INADDR.xml',

description => 'utl_inaddr',

principal => 'TEST',

is_grant => TRUE,

privilege => 'resolve'

);

commit;

end;

/

(2)Add Privilege

begin

dbms_network_acl_admin.add_privilege (

acl => 'UTL_INADDR.xml',

principal => 'TEST',

is_grant => TRUE,

privilege => 'connect'

);

commit;

end;

/

(3)Assign ACL

BEGIN

DBMS_NETWORK_ACL_ADMIN.assign_acl (

acl? ?? ?? ?=> 'UTL_INADDR.xml',

host? ?? ???=> '192.168.168.15');

COMMIT;

END;

/

現在的問題是，如果 host? ?? ???=> '192.168.168.15'); 在test用戶中執行 select UTL_INADDR.GET_HOST_ADDRESS DST_IP from dual; 顯示同最上面的錯誤信息，

如果 host? ?? ???=> '*' );??在test用戶里執行 select UTL_INADDR.GET_HOST_ADDRESS DST_IP from dual; 就正常執行成功，顯示本機的IP地址

由于現在的數據庫中已經有一個host是*的記錄，再增加“host? ?? ???=> '*'”的記錄會把原來的記錄覆蓋掉，所以host一定不能再指定 ”*“ ，可是我怎么設IP地址都不行，都報上面的錯誤，加IP網段、主機名都報同樣的錯誤。

哪位高手對這個比較熟悉， 我要怎么配 host，才可以指定IP 地址，而不用*。謝謝！

操作過程如下：

SQL>

SQL> conn TEST/TEST

Connected.

SQL>

SQL>

SQL> select UTL_INADDR.GET_HOST_ADDRESS DST_IP from dual;

select UTL_INADDR.GET_HOST_ADDRESS DST_IP 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

SQL>

SQL>

SQL> conn / as sysdba

Connected.

SQL> begin

2??dbms_network_acl_admin.create_acl (

3??acl => 'UTL_INADDR.xml',

4??description => 'utl_inaddr',

5??principal => 'TEST',

6??is_grant => TRUE,

7??privilege => 'resolve'

8??);

9??commit;

10??end;

11??/

PL/SQL procedure successfully completed.

SQL> COLUMN host FORMAT A20

SQL> COLUMN acl FORMAT A20

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;

HOST? ?? ?? ?? ?? ???LOWER_PORT UPPER_PORT ACL

-------------------- ---------- ---------- --------------------

*? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?/sys/acls/OracleEBS.xml

SQL> begin

2??dbms_network_acl_admin.add_privilege (

3??acl => 'UTL_INADDR.xml',

4??principal => 'TEST',

5??is_grant => TRUE,

6??privilege => 'connect'

7??);

8??commit;

9??end;

10??/

PL/SQL procedure successfully completed.

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;

HOST? ?? ?? ?? ?? ???LOWER_PORT UPPER_PORT ACL

-------------------- ---------- ---------- --------------------

*? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?/sys/acls/OracleEBS.xml

SQL> COLUMN acl FORMAT A20

SQL> COLUMN principal FORMAT A20

SQL> SELECT acl,

2? ?? ?? ?principal,

3? ?? ?? ?privilege,

4? ?? ?? ?is_grant,

5? ?? ?? ?TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

6? ?? ?? ?TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

7??FROM? ?dba_network_acl_privileges;

ACL? ?? ?? ?? ?? ?? ?? ?PRINCIPAL? ?? ?? ?? ?PRIVILE IS_GRANT? ?START_DATE

--------------------? ? -------------------- ------- ---------- --------------

END_DATE

--------------

/sys/acls/OracleEBS.xml? ? APPS? ?? ?? ?? ?? ???resolve true

/sys/acls/OracleEBS.xml? ? APPS? ?? ?? ?? ?? ???connect true

SQL> begin

2??dbms_network_acl_admin.assign_acl(

3??acl => 'UTL_INADDR.xml',

4??host => '192.168.168.15'

5??);

6??commit;

7??end;

8??/

PL/SQL procedure successfully completed.

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;

HOST? ?? ?? ?? ?? ???LOWER_PORT UPPER_PORT ACL

-------------------- ---------- ---------- --------------------

*? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?/sys/acls/OracleEBS.xml

192.168.168.15? ?? ?? ?? ?? ?? ?? ?? ?? ???/sys/acls/UTL_INADDR.xml

SQL>

SQL> COLUMN acl FORMAT A20

SQL> COLUMN principal FORMAT A20

SQL> SELECT acl,

2? ?? ?? ?principal,

3? ?? ?? ?privilege,

4? ?? ?? ?is_grant,

5? ?? ?? ?TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

6? ?? ?? ?TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

7??FROM? ?dba_network_acl_privileges;

ACL? ?? ?? ?? ?? ?? ?? ???PRINCIPAL? ?? ?? ?? ?PRIVILE IS_GRANT? ?START_DATE

--------------------? ?? ?-------------------- ------- ---------- --------------

END_DATE

--------------

/sys/acls/OracleEBS.xml? ?? ?APPS? ?? ?? ?? ?? ???resolve true

/sys/acls/OracleEBS.xml? ?? ?APPS? ?? ?? ?? ?? ???connect true

/sys/acls/UTL_INADDR.xml? ?? ? TEST? ?? ?? ?? ?resolve true

/sys/acls/UTL_INADDR.xml? ?? ?TEST? ?? ?? ?? ?connect true

SQL>

SQL> select UTL_INADDR.GET_HOST_ADDRESS DST_IP from dual;

select UTL_INADDR.GET_HOST_ADDRESS DST_IP 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

總結

以上是生活随笔為你收集整理的oracle utl inaddr,oracle11gr2中ACL对UTL_INADDR的授权报错，急急急的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。