Wrong ACL file is executed

[GasparYYC]

The old 5.0 version of the ACL is installed and run so it fails to grant the right privileges, the problem is that it references user apex_050000 which doesn't exist anymore with APEX 5.1

Must use the new script provided by Oracle on Application Express Installation Guide Sec 6.6.2 BUT it has a typo, the username apex_050100 must be changed for APEX_050100 otherwise the following error shows while executing: ERROR at line 1: ORA-01435: user does not exist ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 644 ORA-06512: at line 10

In a nutshell, use the following code, it works, been tested..

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_050100
  -- the "connect" privilege if APEX_050100 does not have the privilege yet.

  SELECT ACL INTO 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(ACL_PATH, 'APEX_050100', 'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 'APEX_050100', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml', 'ACL that lets power users to connect to everywhere', 'APEX_050100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

[tschf]

The ACL script uses an API call to get the current apex schema. Relevant line: https://github.com/OraOpenSource/OXAR/blob/master/apex/apex_acl_all.sql#L17

l_apex_username := apex_application.g_flow_schema_owner;

I did a quick glance at the documentation you linked to, to our existing ACL script - and it looked much the same.

(Related? #206)

[GasparYYC]

@tschf I agree, but APEX created ACL errors... So obviously something didn't work during the install.

I didn't check the status of the ACL, I just ran the file provided by Oracle (as per the documentation) and that's when I noticed the typo, I fixed that and voila! Smooth emailing from APEX Applications.

About your comment that it uses: l_apex_username := apex_application.g_flow_schema_owner;

I just ran this in SQLWorkshop:

declare
  l_apex_username all_users.username%type;
begin
 l_apex_username := apex_application.g_flow_schema_owner;
 htp.prn(l_apex_username);
end;

And it returns: APEX_050100

So I the logic seems solid, I don't have any good indication on why it failed... During install I didn't spot any errors and no errors were reported.

NOTE: I built another OXAR server about a month ago and that one works fine.. I really don't know what's with the one that failed cause I built it just when APEX 5.1 became available so its not too old but possibly was built before the script apex_acl_all.sql was created....

[GasparYYC]

Update: I have built a number of OXAR installations and have not had any issues. I am closing as this is not an issue anymore and no one else have reported issues.