oracle utl inaddr,oracle11gr2中ACL对UTL_INADDR的授权报错，急急急

本帖最后由 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