ACL question

[Storey, Robert (DCSO)]

Okay you ACL smart folks.

I'm new to ACL. I have a package that allows me to perform basic FTP using UTL_TCP and such. I created the acl using the SYS user so it owns it. I assigned an acl name and principle (sys). I then added the connect and resolve privileges for Sys to the ACL. I repeated this for SYSTEM.

I have an IP address that I do my FTP to. I did the Assign_acl command and assigned the IP to the ACL along with 21 as the upper and lower limit of the port.

A check of dba_network_acls shows the ACL exists and has the correct IP listed for HOST, the correct port numbers, and the correct ACL. A check of _acl_privileges shows the correct privileges for the sys user.

When I attempt to open the connection using UTL_TCP.OPEN_CONNECTION with the host and port number, I get the ORA-024247 error about ACL permissions. I can't get past this error.

I also checked the ACL via the EM page. It shows the ACL in its list, but, when I view it, I don't see any users listed in principle. It shows Sys as the owner, but is not listied as part of the principles. Nor are the other 2 users I added privileges for.

Oh, and from the command line on the server I can FTP easily to the destination site. I am running toad from my workstation, connected to the database, and executing my test scripts there.

What am I missing? It's gotta be something simple....

[Norman Dunbar]

Hi Robert,

I'm definitely not an ACL smart folk, but maybe this blog post of mine from February 2013, when I last did any ACL stuff, _might_ help.

http://qdosmsq.dunbar-it.co.uk/blog/2013/02/cannot-send-emails-or-read-web-servers-from-oracle-11g/

It's email based, but should be adaptable for ftp. (I think!)

Good luck.


Cheers,
Norm.

[Storey, Robert (DCSO)]

Thanks. I actually found Tim’s post you had linked in your blog. The frustrating thing is that I have created and assigned everything in the same manner as above and I’m still getting the Ora-024247 error.

From: Norman Dunbar [mailto:***@dunbar-it.co.uk]
Sent: Wednesday, August 29, 2018 4:17 PM
To: Storey, Robert (DCSO); Storey, Robert (DCSO); oracle-***@freelists.org
Subject: Re: ACL question

Hi Robert,

I'm definitely not an ACL smart folk, but maybe this blog post of mine from February 2013, when I last did any ACL stuff, _might_ help.

http://qdosmsq.dunbar-it.co.uk/blog/2013/02/cannot-send-emails-or-read-web-servers-from-oracle-11g/

It's email based, but should be adaptable for ftp. (I think!)

Good luck.


Cheers,
Norm.

[Stefan Knecht]

What database version is this?



On Thu, Aug 30, 2018 at 7:31 PM, Storey, Robert (DCSO) <

Post by Storey, Robert (DCSO)
Thanks. I actually found Tim’s post you had linked in your blog. The
frustrating thing is that I have created and assigned everything in the
same manner as above and I’m still getting the Ora-024247 error.
*Sent:* Wednesday, August 29, 2018 4:17 PM
*Subject:* Re: ACL question
Hi Robert,
I'm definitely not an ACL smart folk, but maybe this blog post of mine
from February 2013, when I last did any ACL stuff, _might_ help.
http://qdosmsq.dunbar-it.co.uk/blog/2013/02/cannot-send-
emails-or-read-web-servers-from-oracle-11g/
It's email based, but should be adaptable for ftp. (I think!)
Good luck.
Cheers,
Norm.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

[Stefan Knecht]

It would help if you could dump the ACLs you have created (e.g. your calls
to dbms_network_acl_admin and perhaps output of the data dictionary to show
the ACL).

What's frequently tripped me over is not adding "resolve" as well as
"connect".



On Thu, Aug 30, 2018 at 1:53 AM, Storey, Robert (DCSO) <

Post by Storey, Robert (DCSO)
Okay you ACL smart folks.
I’m new to ACL. I have a package that allows me to perform basic FTP
using UTL_TCP and such. I created the acl using the SYS user so it owns
it. I assigned an acl name and principle (sys). I then added the connect
and resolve privileges for Sys to the ACL. I repeated this for SYSTEM.
I have an IP address that I do my FTP to. I did the Assign_acl command
and assigned the IP to the ACL along with 21 as the upper and lower limit
of the port.
A check of dba_network_acls shows the ACL exists and has the correct IP
listed for HOST, the correct port numbers, and the correct ACL. A check of
_*acl*_privileges shows the correct privileges for the sys user.
When I attempt to open the connection using UTL_TCP.OPEN_CONNECTION with
the host and port number, I get the ORA-024247 error about ACL
permissions. I can’t get past this error.
I also checked the ACL via the EM page. It shows the ACL in its list,
but, when I view it, I don’t see any users listed in principle. It shows
Sys as the owner, but is not listied as part of the principles. Nor are the
other 2 users I added privileges for.
Oh, and from the command line on the server I can FTP easily to the
destination site. I am running toad from my workstation, connected to the
database, and executing my test scripts there.
What am I missing? It’s gotta be something simple
.

[Storey, Robert (DCSO)]

I have resolve added to the privileges as well as connect. Which I thought was redundant since the documentation I read said that the privilege of connect included resolve as well?

From: Stefan Knecht [mailto:***@gmail.com]
Sent: Wednesday, August 29, 2018 9:54 PM
To: Storey, Robert (DCSO)
Cc: oracle-***@freelists.org
Subject: Re: ACL question

It would help if you could dump the ACLs you have created (e.g. your calls to dbms_network_acl_admin and perhaps output of the data dictionary to show the ACL).

What's frequently tripped me over is not adding "resolve" as well as "connect".



On Thu, Aug 30, 2018 at 1:53 AM, Storey, Robert (DCSO) <***@dcso.nashville.org<mailto:***@dcso.nashville.org>> wrote:
Okay you ACL smart folks.

I’m new to ACL. I have a package that allows me to perform basic FTP using UTL_TCP and such. I created the acl using the SYS user so it owns it. I assigned an acl name and principle (sys). I then added the connect and resolve privileges for Sys to the ACL. I repeated this for SYSTEM.

I have an IP address that I do my FTP to. I did the Assign_acl command and assigned the IP to the ACL along with 21 as the upper and lower limit of the port.

A check of dba_network_acls shows the ACL exists and has the correct IP listed for HOST, the correct port numbers, and the correct ACL. A check of _acl_privileges shows the correct privileges for the sys user.
When I attempt to open the connection using UTL_TCP.OPEN_CONNECTION with the host and port number, I get the ORA-024247 error about ACL permissions. I can’t get past this error.

I also checked the ACL via the EM page. It shows the ACL in its list, but, when I view it, I don’t see any users listed in principle. It shows Sys as the owner, but is not listied as part of the principles. Nor are the other 2 users I added privileges for.

Oh, and from the command line on the server I can FTP easily to the destination site. I am running toad from my workstation, connected to the database, and executing my test scripts there.

What am I missing? It’s gotta be something simple
.