4 Replies Latest reply: Feb 13, 2013 2:57 PM by TexasApexDeveloper RSS

    Apex 4.0 Granting Connect Privileges

    Merlin128
      on my 11.1.0.6 Database I was able to run the "Granting Connect Priviles" code below..
      and it works..

      but on my 11.1.0.7 gives me an error:
      ORA-30554: funciton-based index XDB.XEB$ACL_XIDX is disabled



      CODE (FROM INSTALL INSTRUCTIONS):


      DECLARE
      ACL_PATH VARCHAR2(4000);
      ACL_ID RAW(16);
      BEGIN
      -- Look for the ACL currently assigned to '*' and give APEX_040000
      -- the "connect" privilege if APEX_040000 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;
      -- Before checking the privilege, ensure that the ACL is valid
      -- (for example, does not contain stale references to dropped users).
      -- If it does, the following exception will be raised:
      --
      -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
      -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
      --
      SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
      FROM XDB.XDB$ACL A, PATH_VIEW P
      WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
      EQUALS_PATH(P.RES, ACL_PATH) = 1;
      DBMS_XDBZ.ValidateACL(ACL_ID);
      IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000',
      'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
      'APEX_040000', 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_040000', TRUE, 'connect');
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
      END;
      /
      COMMIT;