Skip to Main Content
  • Questions
  • Getting error while trying to connect SFTP using UTL_TCP package in Oracle Cloud ATP Database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Selva.

Asked: July 06, 2024 - 4:51 pm UTC

Last updated: July 30, 2024 - 2:57 am UTC

Version: Oracle cloud ATP database

Viewed 1000+ times

You Asked

I am trying to conenct the SFTP server usign UTL_TCP package. Please find below my code. I have configured the ACL and added the CONNECT privilage as well.


declare
    c  utl_tcp.connection;
    ret_val varchar2(100);
    reading varchar2(100);
    cmd varchar2(100) := 'COMMAND(STUFF,SERVICE,EXPECTS)';
    cmd2 varchar2(100);
begin
    c := utl_tcp.open_connection(remote_host => 'sftp.greystar.com'
                               ,remote_port =>  9995
                               ,charset     => 'US7ASCII'
                               ,tx_timeout  => 10
                               );
    ret_val := utl_tcp.write_line(c, cmd);
    ret_val := utl_tcp.write_line(c);
  
    dbms_output.put_line(utl_tcp.get_text(c, 100));  
    sys.dbms_session.sleep(1);
    cmd2 := 'POLL(' || ret_val || ')';
    reading := utl_tcp.write_line(c, cmd2);
    reading := utl_tcp.write_line(c);
    dbms_output.put_line(utl_tcp.get_text(c, 100));
    utl_tcp.close_connection(c);  --Close the connection
END;


But I am getting the below error.

Error report -
ORA-01031: insufficient privileges
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 310
ORA-06512: at line 8
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges



and Connor said...

From the docs https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-plsql-packages.html

UTL_TCP

- The IP address is not allowed in the host name.

- The only allowed ports are: 443 (HTTP) 25 and 587 (SMTP).

- For port 443, only HTTPS URLs are allowed.

- The WALLET_PATH and WALLET_PASSWORD arguments for the OPEN_CONNECTION procedure are ignored. The default value for the WALLET_PATH and WALLET_PASSWORD property are set to the wallet that is used by UTL_HTTP and DBMS_CLOUD for making outbound web requests on Autonomous Database.

- UTL_TCP usage is audited by default. You cannot disable auditing for UTL_TCP.

- SSL/TLS is enforced for all communication happening over TCP/IP connections.

- When your Autonomous Database instance is configured with a private endpoint, set the ROUTE_OUTBOUND_CONNECTIONS database parameter to 'PRIVATE_ENDPOINT' to specify that all outgoing UTL_TCP connections are subject to the Autonomous Database instance private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.