Fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB is one of the big changes introduced with Oracle Database 11g.
What was controlled in the past through simple execute privilege, now is controlled through access control list (ACL), that requires Oracle XML DB installed.
A database supported by my team has a stored procedure that uses the package UTL_INADDR. This stored procedure is used by the application to retrieve the local database server IP during user log in.
As a required task, Fine-Grained access to external network services was configured after upgrading the database to 11g, and everything was working perfectly, until it failed :o( Suddenly it just stopped to work, with the application receiving the error ORA-24247: network access denied by access control list (ACL) when a user was trying to establish a new connection to the database.
When I was engaged, my first thought was: well, someone removed the access. For my surprise it was not removed, but there was a new ACL: oracle-sysman-ocm-Resolve-Access.xml - take a look at the events that led to the error:
1- The stored procedure calls the function UTL_INADDR.get_host_address without any parameter. So it will return the IP address of the local host.
2- After upgrading the database a new Access Control List (ACL) was created and the privilege "resolve" was granted to the application user.
3- That new ACL was assigned to the database host computer using an * as the host name, instead of using the individual host name with its domain.
4- Months later, the primary DBA for the database was working with Oracle to resolve an ORA-600 error. He was asked to run the Remote Diagnostic Agent (RDA).
5- RDA was installed and set to configure Oracle Configuration Manager (OCM) in connected mode. At this point the problem started, as OCM created the ACL oracle-sysman-ocm-Resolve-Access.xml, and assigned it to the database host using its name.
From Oracle documentation: "The ACL assigned to a domain takes a lower precedence than the other ACLs assigned to sub-domains, which take a lower precedence than the ACLs assigned to the individual hosts". For example, the following hosts are listed in decreasing order of precedence:
localhost.localdomain
*.localdomain
*
Therefore, the ACL oracle-sysman-ocm-Resolve-Access.xml took precedence over the ACL created after the database upgrade, and as the application user was not part of oracle-sysman-ocm-Resolve-Access.xml, Oracle was just working as it should, i.e., denying the access for a non-authorized user.
Probably the reason for using * instead of the host name was a misunderstanding about what host names and IP addresses the application could retrieve - think about a request like this: the application must be able to retrieve the host name or IP adress of any remote host.
Well, wildcards are not villains (I cannot imagine UNIX without them), we just need to take care about how to use them.
Good to get you here.
ReplyDeleteFarooq Nafey