2

I have met a problem with my database (version is Oracle 11g).

What did I want to do?

  • i want to use procedures to upload a file to the ftp server.

How did i do?

  • first , i have download the ftp.pks and ftp.pkb files and I use @**.pks to import this packages.

  • and then i code the procedures like this:

    DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('192.168.1.102', '21', 'tony', 'tony');
  ftp.ascii(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'aaa.txt',
          p_to_file   => 'test_put.txt');
  ftp.logout(l_conn);
END;

  • when I run it, the console give me this error information

    ORA-24247: network access rejected by ACL
    ORA-06512: at "SYS.UTL_TCP", line 17
    ORA-06512: at "SYS.UTL_TCP", line 246
    ORA-06512: at "SCOTT.FTP", line 76
    ORA-06512: at line 4

  • and for this reason, I created the ACL rules with the following code:

    begin

     DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
     acl          => 'ftp_conn.xml',
     description  => 'ftp connection',
     principal    => 'SCOTT',
     is_grant     => TRUE,
     privilege    => 'connect');

     DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
      acl         => 'ftp_conn.xml',
      host        => '192.168.1.102',
      lower_port  => 21,
      upper_port => 21);

end;

But, when I run the ftp code again, it also shows that error, so I want know how to fix this.

ACL can allow TCP connection, but when i use ftp package and call 'ftp.put', The console output is

error at 1 line:
ORA-29260: network error:not connected
ORA-06512: at "SYS.UTL_TCP", line 212
ORA-06512: at "SYS.UTL_TCP", line 432
ORA-06512: at "SCOTT.FTP", line 413
ORA-24247: Network access rejected by acl
ORA-06512: at "SCOTT.FTP", line 491
ORA-06512: at line 6
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Tony
  • 398
  • 2
  • 11
  • Did you commit after asigning the ACL ? – A.B.Cade Mar 26 '12 at 09:29
  • @A.B.Cade yes,i have commit the changes. – Tony Mar 26 '12 at 09:42
  • may this help ? stackoverflow.com/questions/9827038/oracle-11g-migrated-users-have-acl-set-but-cant-hit-utl-http-ora-24247-29273/9858759 – A.B.Cade Mar 26 '12 at 11:17
  • @A.B.Cade hello .i found that i can use tcp connetion,but the when i use ftp package,it will raise error. – Tony Mar 26 '12 at 12:13
  • "Network access rejected by acl" sounds that another port is blocked, for some reason. With some FTP client it works fine? –  Mar 26 '12 at 16:16
  • @SérgioMichels Yeah,I can connected the ftp server with console.and also use ftp.login can connected.but when i use ftp.put,it raise this error. – Tony Mar 27 '12 at 00:41

1 Answers1

0

Try to add the resolve privilege (though it sounds strange...): 

begin
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
 acl          => 'ftp_conn.xml',
 description  => 'ftp connection',
 principal    => 'SCOTT',
 is_grant     => TRUE,
 privilege    => 'connect');

 DBMS_NETWORK_ACL_ADMIN.add_privilege ( 
acl         => 'ftp_conn.xml', 
principal   => 'SCOTT',
is_grant    => FALSE, 
privilege   => 'resolve', 
position    => NULL, 
start_date  => NULL,
end_date    => NULL);

 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
  acl         => 'ftp_conn.xml',
  host        => '192.168.1.102',
  lower_port  => 21,
  upper_port => 21);

end;
/
COMMIT;
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • 1
    i am so sorry ,the error information format is not pretty
    error at 1 line: ORA-29260: network error:not connected ORA-06512: at "SYS.UTL_TCP", line 212 ORA-06512: at "SYS.UTL_TCP", line 432 ORA-06512: at "SCOTT.FTP", line 413 ORA-24247: Network access rejected by acl ORA-06512: at "SCOTT.FTP", line 491 ORA-06512: at line 6     – Tony Mar 26 '12 at 10:00