Saving User Role Assignments

Question

It is possible to save User Role Assignments? When I replace application or remove it and add new with same application id, Apex removes User Role Assignments from APEX_APPL_ACL_USERS table. Actually I can create custom table to store User Role Assigments and later use to insert in APEX_APPL_ACL_USERS table, but I think isn't good solution.

Answer 1

There currently isn't. The safest way is to backup your user roles before replacing the app. I found that when I do an import through the UI the users are preserved, when you delete the app the users are gone. There is a great blog about how to generate a script to restore user roles. For a single app, here is a script (mostly taken from above blog). Replace the 12345 with your application id. It generates a script that you have to execute after you have recreated your application.

WITH application_id (app_id) AS
(SELECT 12345 FROM dual)
SELECT txt
   FROM (SELECT 1 x, 'BEGIN' txt FROM DUAL
         UNION ALL
         SELECT 2,
                   RPAD(' ', 3)
                || CASE
                       WHEN rn = 1 THEN REPLACE(q'[APEX_UTIL.set_workspace('~workspace~');]', '~workspace~', workspace)
                   END
                || REPLACE(
                       REPLACE(
                           REPLACE(
                               q'[APEX_ACL.ADD_USER_ROLE(p_application_id=>~app~,p_user_name=>'~user~',p_role_static_id=>'~role~');]',
                               '~app~',
                               application_id),
                           '~user~',
                           user_name),
                       '~role~',
                       role_static_id) txt
           FROM (SELECT workspace,
                        ROW_NUMBER() OVER(PARTITION BY workspace ORDER BY application_id, user_name, role_static_id) rn,
                        application_id,
                        user_name,
                        role_static_id
                   FROM apex_appl_acl_user_roles 
                        JOIN application_id a ON a.app_id = application_id)
         UNION ALL
         SELECT 3, '   COMMIT;' FROM DUAL
         UNION ALL
         SELECT 4, 'END;' FROM DUAL)
  ORDER BY x;