To avoid the following kind of Oracle Error ORA-24247: network access denied by access control list (ACL)
you should read this article.
For security reasons, since Oracle 11 network services are disabled by default. To enable Apex for sending out emails, use other web services or print server you must use the DBMS_NETWORK_ACL_ADMIN
package to grant certain privileges to the related hosts for the parsing schema and the Apex repository schema, like APEX_050000. It depends lastly which kind of package you use.
Please note there is a difference between Oracle 11g and Oracle 12c on how to grant the Access Control Lists (ACL). Let me point out the distinction.
All what i mention here is valid for the following objects:
- DBMS_DEBUG_JDWP
- DBMS_LDAP, APEX_LDAP
- httpuritype
- UTL_INADDR
- UTL_HTTP
- UTL_MAIL, APEX_MAIL
- UTL_SMTP
- UTL_TCP
- WWV_FLOW_WEBSERVICES_API, APEX_WEB_SERVICE
As default these objects are granted to PUBLIC. Because some DBAs revoke the execute rights from PUBLIC you should check this behaviour on your database. For instance:
SELECT grantee ,table_name ,privilege ,owner FROM dba_tab_privs WHERE table_name IN ('DBMS_DEBUG_JDWP' ,'DBMS_LDAP' ,'httpuritype' ,'UTL_INADDR' ,'UTL_HTTP' ,'UTL_MAIL' ,'UTL_SMTP' ,'UTL_TCP' ,'WWV_FLOW_WEBSERVICES_API' );
By the way, if the object UTL_MAIL doesn’t exist (check it via DBA_OBJECTS), you can install it with SYS rights. Hence these two scripts have to execute for making UTL_MAIL available:
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
Is user PUBLIC or your parsing Schema not listet in the above Select statement for the objects you need, then execute as a user with the corresponding rights, for example SYS this statement:
GRANT EXECUTE ON object_name TO [parsing schema];
The owner of the package WWV_FLOW_WEBSERVICES_API
is the Apex Schema, like APEX_050000. You can use the public synonym APEX_WEB_SERVICE
in your Apex parsing schema to create web services. This Link has more information.
Let us start with the grants for an Oracle 12c database before we go over to an Oracle 11g. Please run all the following SQL snippets and SQL scripts as SYS user.
Grant privilegs for an Oracle 12c Database
With Oracle 12c an Access Control List (ACL) will never create directly. The call of the procedure DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
creates an ACL implicitly. This means, if an ACL already exists, then an Access Control Entry (ACE) will be appended. Otherwise, if there is no ACL then it will be created together with the ACE.
For example to create an ACL or append an ACE for a complete network access from database to all remote hosts, you have to set the host parameter to ‘*’. This is for sure a security risk but it is ok for a test or other circumstances.
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => '*' ,lower_port => null ,upper_port => null ,ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve') ,principal_name => 'APEX_050000' ,principal_type => xs_acl.ptype_db) );
Repeat this SQL for your parsing schema in Apex.
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => '*' ,ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve') ,principal_name => 'MY_PARSING_SCHEMA' ,principal_type => xs_acl.ptype_db) );
The default NULL values for lower and upper port are not required.
And that is it now. We need not more. The ACLs have been extremely simplified. Here are some parameter explanations.
host parameter
The host
parameter can be a “host name” or an “IP address” of the host. You can use a wildcard to specify a domain or an IP subnet, for example:
- ‘*.oracle-web.com’
- ‘192.0.2.*’
lower_port/upper_port parameter
lower_port specifies the lower boundary and upper_port the upper boundary of the port range. The default is null, which means that there is no port restriction, i.e. the ACL applies to all ports. The range of port numbers is between 1 and 65535.
The resolve
privilege in the privilege_list has no effect when a port range is specified in the ACL assignment.
ace parameter
As you can see in the above procedure call, ACE is defined by using the XS$ACE_TYPE
constant. There are three parameter "privilege_list, principal_name and principal_type"
.
A privilege list can be:
connect | Grants the user permission to connect a network service to a host for all packages. |
resolve | Resolves a network host name or IP address through the UTL_INADDR package. |
smtp | Sends SMTP to a host through the UTL_SMTP and UTL_MAIL packages. |
http | Makes an HTTP request to a host through the UTL_HTTP package and the HttpUriType type. |
http_proxy | Makes an HTTP request through a proxy with UTL_HTTP package and HttpUriType type. You must include http_proxy in conjunction to the http privilege if the user makes the HTTP request through a proxy. |
jdwp | Used for Java Debug Wire Protocol debugging operations for Java or PL/SQL stored procedures. See Configuring Network Access for Java Debug Wire Protocol Operations for more information. Used in conjunction with DBMS_DEBUG_JDWP. |
A principal name can be:
Database User or Role | Include only one schema or role per DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE call, for example first the parsing schema and in a second call the APEX-Schema. This value is case insensistive, unless you enter it in double quotation marks (for example, ‘”APEX_050000′”). |
A principal type can be:
XS_ACL.PTYPE_DB | Enter XS_ACL.PTYPE_DB for a database user or role. |
XS_ACL.PTYPE_XS | Is used to specify an Oracle Database Real Application Security application user. |
An Access Control List (ACL) has zero or many Access Control Entries (ACE). Both is selectable via these statements:
ACL:
select host, lower_port, upper_port, acl from dba_host_acls;
ACE:
select ace_order, privilege, grant_type, principal, host from dba_host_aces;
Grant privilegs for an Oracle 11g Database
The approach is quite different:
- create an access control list, if the list has not been already created
- add privileges to the user or the role which uses the network resources
- assign the acl to a more or less specific address
Please have in mind, the Oracle documentation says, that these procedures are deprecated in Oracle Database 12c. The procedures remain available in the package for reasons of backward compatibility. Therefore if you have an Oracle 12c Database or above don’t use the following, subjacent procedures.
But suppose you have an Oracle 11gR1/2 or an Oracle XE 11gR1/2, in this case pursue the text. The ACLs will be saved in the internal XML-DB and we start with the creation of an Access Control List.
BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'across_broder.xml' -- any name you want ,description => 'any HTTP access' ,principal => 'HR' -- is case sensitive. ,is_grant => TRUE ,privilege => 'connect' ); COMMIT; END;
Beside the connect
privilege you can also add the resolve
privilege. This is for resolving computer names into IP addresses. In other words, if you don’t use computer names but only IP addresses, resolve
privilege is not necessary.
Anyway, now we want to add resolve
for user/schema HR.
BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => 'across_broder.xml' -- same ACL Name as in create_acl proc. ,principal => 'HR' -- is case sensitive. ,is_grant => TRUE ,privilege => 'resolve' ); COMMIT; END; /
And assigning one or more hosts to the ACL will be realized with:
BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'across_broder.xml' ,host => '*.oracle-web.com' ); COMMIT; END; /
This means, any request for oracle-web.com will be accepted. Furthermore it is possible to restrict the host(s) to a specific port range, like this for oracle.com
BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'across_broder.xml' ,host => '*.oracle.com' ,lower_port => 80 -- port 80 to 100 will be accepted ,upper_port => 100 -- port 80 to 100 will be accepted ); COMMIT; END; /
The opposite of the ADD_PRIVILEGE
procedure is:
BEGIN DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE ( acl => 'across_broder.xml' ,principal => 'HR' -- is case sensitive. ,privilege => 'connect' ); COMMIT; END; /
Connect privilege will be deleted for schema HR from across_broder.xml
Access Control List.
The opposite of the ASSIGN_ACL
procedure is:
BEGIN DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL( acl => 'across_broder.xml' ,host => '*.oracle-web.com' ); COMMIT; END; /
The assignment for *.oracle-web.com
will be removed from the across_broder.xml
.
And lastly the opposite of the CREATE_ACL
procedure is:
BEGIN DBMS_NETWORK_ACL_ADMIN.DROP_ACL (acl => 'across_broder.xml'); COMMIT; END; /
across_broder.xml
does not longer exist.
To check if a privilege is granted or denied for an user in an ACL, you can use this function:
SELECT DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE( 'across_broder.xml' ,'HR' -- is case sensitive. ,'connect') from dual;
It returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
A nice script to get in contact with a certain host, like “*.oracle-web.com”:
CREATE OR REPLACE PROCEDURE create_acl_proc IS BEGIN $IF DBMS_DB_VERSION.VER_LE_10_2 $THEN NULL; $ELSIF DBMS_DB_VERSION.VER_LE_11 $THEN BEGIN DBMS_NETWORK_ACL_ADMIN.DROP_ACL( acl => 'across_broder.xml' ); EXCEPTION WHEN OTHERS THEN NULL; END; DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => 'across_broder.xml' ,description => 'connects for HR user' ,principal => 'HR' -- adjust principal to your requirements ,is_grant => true ,privilege => 'connect' ); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( acl => 'across_broder.xml' ,principal => 'HR' -- adjust principal to your requirements ,is_grant => true ,privilege => 'resolve' ); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'across_broder.xml' ,host => '*.oracle-web.com' -- adjust host to your requirements ); $END END; /
The parameter Prinicipal can be a user or a role. If you want open the gate for all users, set principal to ‘PUBLIC’. Please be aware, this is most likely a security risk.
More information are accessible via these two DBA views:
SELECT * FROM DBA_NETWORK_ACLS; SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;
Open the service doors for Apex in an Oracle 11 database
To give access for print server, to use web services or sending out emails from Apex execute the following script:
DECLARE v_acl_path VARCHAR2(4000); BEGIN ----------------------------------------------------------------- -- Look for the ACL currently assigned to '*' (means all hosts) -- and give APEX_050000 the "connect" privilege -- if APEX_050000 does not have the privilege yet. ----------------------------------------------------------------- SELECT ACL INTO v_acl_path FROM DBA_NETWORK_ACLS WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL; IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(v_acl_path ,'APEX_050000' ,'connect') IS NULL THEN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( v_acl_path ,'APEX_050000' ,TRUE ,'connect' ); END IF; EXCEPTION -------------------------------------------------------- -- When no ACL has been assigned to '*', then do it yet -------------------------------------------------------- WHEN NO_DATA_FOUND THEN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( 'across_broder.xml' ,'ACL that lets certain schemas connect to everywhere' ,'APEX_050000' ,TRUE ,'connect' ); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( acl => 'across_broder.xml' ,principal => 'APEX_050000' ,is_grant => true ,privilege => 'resolve' ); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( 'across_broder.xml' ,'*' ); END; / commit;
And again, as mentioned in the beginning, the Apex Schema, like APEX_050000 and the parsing schema need the grant ACL privileges to work without any errors like ORA-24247: network access denied by access control list (ACL).