From the APEX Developer’s Guide:
When you export an application with the Access Control feature, the application roles, Administrator, Contributor, and Reader, are exported. However, the users assigned to these roles are not exported.
[spelling correction “your”-“you” mine]
If the export option won’t put the users into a script for deployment, the obvious question then is: “How do I get those users into a script?”
Fortunately, APEX provides views to expose user-role assignments. To assign a role programmatically you need an application, a user and a role for the APEX_ACL.ADD_USER_ROLE procedure. The procedure is overloaded, allowing the role to be passed by numeric ROLE_ID or text ROLE_STATIC_ID. I prefer to use the text value because it helps to be self-documenting as to intent. Selecting the relevant columns from the APEX view yields the necessary values. The workspace name isn’t needed for the ADD_USER_ROLE procedure, but we’ll make use of it later on to create a deployment script that will set the workspace with APEX_UTIL.
SQL> SELECT workspace,
2 application_id,
3 user_name,
4 role_static_id
5 FROM apex_appl_acl_user_roles
6 ORDER BY workspace, application_id, user_name;
WORKSPACE APPLICATION_ID USER_NAME ROLE_STATIC_ID
--------------- ----------------- ------------ -----------------
TEST_WORKSPACE1 101 TESTUSER2 CONTRIBUTOR
TEST_WORKSPACE1 101 TESTUSER3 ADMINISTRATOR
TEST_WORKSPACE1 110 TESTUSER1 CONTRIBUTOR
TEST_WORKSPACE1 110 TESTUSER3 ADMINISTRATOR
TEST_WORKSPACE1 117 TESTUSER1 SECURITY
TEST_WORKSPACE1 117 TESTUSER1 CONTRIBUTOR
TEST_WORKSPACE1 117 TESTUSER9 CONTRIBUTOR
TEST_WORKSPACE1 117 TESTUSER4 READER
TEST_WORKSPACE1 117 TESTUSER4 SECURITY
TEST_WORKSPACE1 117 TESTUSER4 CONTRIBUTOR
TEST_WORKSPACE1 117 TESTUSER5 CONTRIBUTOR
TEST_WORKSPACE1 117 TESTUSER6 SECURITY
TEST_WORKSPACE1 117 TESTUSER6 READER
TEST_WORKSPACE1 117 TESTUSER7 SECURITY
TEST_WORKSPACE1 117 TESTUSER7 CONTRIBUTOR
TEST_WORKSPACE1 117 TESTUSER8 CONTRIBUTOR
TEST_WORKSPACE1 117 TESTUSER2 CONTRIBUTOR
TEST_WORKSPACE1 117 TESTUSER3 SECURITY
TEST_WORKSPACE1 117 TESTUSER3 CONTRIBUTOR
TEST_WORKSPACE1 150 TESTUSER1 SECURITY
TEST_WORKSPACE1 150 TESTUSER1 CONTRIBUTOR
TEST_WORKSPACE1 150 TESTUSER9 CONTRIBUTOR
TEST_WORKSPACE1 150 TESTUSER4 ADMINISTRATOR
TEST_WORKSPACE2 201 USER1 CONTRIBUTOR
TEST_WORKSPACE2 201 USER1 SECURITY
TEST_WORKSPACE2 201 USER2 CONTRIBUTOR
TEST_WORKSPACE2 201 USER3 CONTRIBUTOR
TEST_WORKSPACE2 201 USER4 CONTRIBUTOR
TEST_WORKSPACE2 201 USER5 READER
Next, we’ll number the rows so we know when each new workspace begins in the list. In the output below I’ve manually inserted a blank line to highlight the change.
SQL>SELECT workspace,
2 application_id,
3 user_name,
4 role_static_id,
5 ROW_NUMBER() OVER(PARTITION BY workspace ORDER BY application_id, user_name, role_static_id) rn
6 FROM apex_appl_acl_user_roles
7 ORDER BY workspace, application_id, user_name;
WORKSPACE APPLICATION_ID USER_NAME ROLE_STATIC_ID RN
--------------- ----------------- ------------ ----------------- ------
TEST_WORKSPACE1 101 TESTUSER2 CONTRIBUTOR 1
TEST_WORKSPACE1 101 TESTUSER3 ADMINISTRATOR 2
TEST_WORKSPACE1 110 TESTUSER1 CONTRIBUTOR 3
TEST_WORKSPACE1 110 TESTUSER3 ADMINISTRATOR 4
TEST_WORKSPACE1 117 TESTUSER1 SECURITY 5
TEST_WORKSPACE1 117 TESTUSER1 CONTRIBUTOR 6
TEST_WORKSPACE1 117 TESTUSER9 CONTRIBUTOR 7
TEST_WORKSPACE1 117 TESTUSER4 READER 8
TEST_WORKSPACE1 117 TESTUSER4 SECURITY 9
TEST_WORKSPACE1 117 TESTUSER4 CONTRIBUTOR 10
TEST_WORKSPACE1 117 TESTUSER5 CONTRIBUTOR 11
TEST_WORKSPACE1 117 TESTUSER6 SECURITY 12
TEST_WORKSPACE1 117 TESTUSER6 READER 13
TEST_WORKSPACE1 117 TESTUSER7 SECURITY 14
TEST_WORKSPACE1 117 TESTUSER7 CONTRIBUTOR 15
TEST_WORKSPACE1 117 TESTUSER8 CONTRIBUTOR 16
TEST_WORKSPACE1 117 TESTUSER2 CONTRIBUTOR 17
TEST_WORKSPACE1 117 TESTUSER3 SECURITY 18
TEST_WORKSPACE1 117 TESTUSER3 CONTRIBUTOR 19
TEST_WORKSPACE1 150 TESTUSER1 SECURITY 20
TEST_WORKSPACE1 150 TESTUSER1 CONTRIBUTOR 21
TEST_WORKSPACE1 150 TESTUSER9 CONTRIBUTOR 22
TEST_WORKSPACE1 150 TESTUSER4 ADMINISTRATOR 23
TEST_WORKSPACE2 201 USER1 CONTRIBUTOR 1
TEST_WORKSPACE2 201 USER1 SECURITY 2
TEST_WORKSPACE2 201 USER2 CONTRIBUTOR 3
TEST_WORKSPACE2 201 USER3 CONTRIBUTOR 4
TEST_WORKSPACE2 201 USER4 CONTRIBUTOR 5
TEST_WORKSPACE2 201 USER5 READER 6
To create a script, for each new workspace (RN=1) we’ll need a call to APEX_UTIL.set_workspace. For every row we’ll generate one call to APEX_ACL.ADD_USER_ROLE.
SQL> SELECT CASE WHEN rn = 1 THEN REPLACE(q'[APEX_UTIL.set_workspace('~workspace~');]', '~workspace~', workspace) END
2 || REPLACE(
3 REPLACE(
4 REPLACE(
5 q'[APEX_ACL.ADD_USER_ROLE(p_application_id=>~app~,p_user_name=>'~user~',p_role_static_id=>'~role~');]',
6 '~app~',
7 application_id),
8 '~user~',
9 user_name),
10 '~role~',
11 role_static_id) txt
12 FROM (SELECT workspace,
13 ROW_NUMBER() OVER(PARTITION BY workspace ORDER BY application_id, user_name, role_static_id) rn,
14 application_id,
15 user_name,
16 role_static_id
17 FROM apex_appl_acl_user_roles);
TXT
-----------------------------------------------------------------------------------------------------------------------------------------------------
APEX_UTIL.set_workspace('TEST_WORKSPACE1');APEX_ACL.ADD_USER_ROLE(p_application_id=>101,p_user_name=>'TESTUSER2',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>101,p_user_name=>'TESTUSER3',p_role_static_id=>'ADMINISTRATOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>110,p_user_name=>'TESTUSER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>110,p_user_name=>'TESTUSER3',p_role_static_id=>'ADMINISTRATOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER1',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER2',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER3',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER3',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER4',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER4',p_role_static_id=>'READER');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER4',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER5',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER6',p_role_static_id=>'READER');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER6',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER7',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER7',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER8',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER9',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER1',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER4',p_role_static_id=>'ADMINISTRATOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER9',p_role_static_id=>'CONTRIBUTOR');
APEX_UTIL.set_workspace('TEST_WORKSPACE2');APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER1',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER2',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER3',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER4',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER5',p_role_static_id=>'READER');
29 rows selected.
Now that we have the body of the script completed all that is left is a COMMIT and wrapping BEGIN/END around it.
SQL> SELECT txt
2 FROM (SELECT 1 x, 'BEGIN' txt FROM DUAL
3 UNION ALL
4 SELECT 2,
5 RPAD(' ', 3)
6 || CASE
7 WHEN rn = 1 THEN REPLACE(q'[APEX_UTIL.set_workspace('~workspace~');]', '~workspace~', workspace)
8 END
9 || REPLACE(
10 REPLACE(
11 REPLACE(
12 q'[APEX_ACL.ADD_USER_ROLE(p_application_id=>~app~,p_user_name=>'~user~',p_role_static_id=>'~role~');]',
13 '~app~',
14 application_id),
15 '~user~',
16 user_name),
17 '~role~',
18 role_static_id) txt
19 FROM (SELECT workspace,
20 ROW_NUMBER() OVER(PARTITION BY workspace ORDER BY application_id, user_name, role_static_id) rn,
21 application_id,
22 user_name,
23 role_static_id
24 FROM apex_appl_acl_user_roles)
25 UNION ALL
26 SELECT 3, ' COMMIT;' FROM DUAL
27 UNION ALL
28 SELECT 4, 'END;' FROM DUAL)
29 ORDER BY x;
TXT
-----------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
APEX_UTIL.set_workspace('TEST_WORKSPACE1');APEX_ACL.ADD_USER_ROLE(p_application_id=>101,p_user_name=>'TESTUSER2',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER5',p_role_static_id=>'READER');
APEX_ACL.ADD_USER_ROLE(p_application_id=>101,p_user_name=>'TESTUSER3',p_role_static_id=>'ADMINISTRATOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>110,p_user_name=>'TESTUSER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>110,p_user_name=>'TESTUSER3',p_role_static_id=>'ADMINISTRATOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER1',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER2',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER3',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER3',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER4',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER4',p_role_static_id=>'READER');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER4',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER5',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER6',p_role_static_id=>'READER');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER6',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER7',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER7',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER8',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>117,p_user_name=>'TESTUSER9',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER1',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER4',p_role_static_id=>'ADMINISTRATOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>150,p_user_name=>'TESTUSER9',p_role_static_id=>'CONTRIBUTOR');
APEX_UTIL.set_workspace('TEST_WORKSPACE2');APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER1',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER1',p_role_static_id=>'SECURITY');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER2',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER3',p_role_static_id=>'CONTRIBUTOR');
APEX_ACL.ADD_USER_ROLE(p_application_id=>201,p_user_name=>'USER4',p_role_static_id=>'CONTRIBUTOR');
COMMIT;
END;
32 rows selected.
I could have skipped all of the setup queries and jumped straight to the last one; but I’ve frequently been asked what my thought processes are as I write queries. This extract was a good opportunity to show how I thought about the task and built up the functionality a piece at a time until arriving at the desired result.
I hope you find the final incarnation useful. You can, of course, filter specific workspaces, applications, and/or users by adding a WHERE clause to the inner most query on the APEX view.
Questions and comments, as always, are welcome.
Thanks for your efforts
I made the below script to copy the roles from the old application as a script and then run it for the new application by substituting the app_id
————————
SELECT ‘BEGIN’ SCRIPT
FROM DUAL
UNION ALL
SELECT ‘APEX_ACL.ADD_USER_ROLE(p_application_id=>:APP_ID,p_user_name=>”’||user_name||”’,p_role_static_id=>”’||role_static_id||”’);’ SCRIPT
FROM apex_appl_acl_user_roles
UNION ALL
SELECT ‘END;’ SCRIPT
FROM DUAL;
I’m glad you found it useful
Thank you!
I have an issue thought, I imported my App into a different Workspace (a dev one) but even if I add myself as an administrator (or reader or contributor) I’m not being able to access it:
”Insufficient privileges, user is not a Contributor” (Contributor or Admin or Read)
Any idea of what might be happening?
Thanks!
The only thing I can think of is the user was added to the ACLs by the script but the user isn’t actually a known user in the APEX workspace – or whatever authentication scheme you use.
I was surprised the ADD_USER_ROLE procedure allows adding invalid users, but it does.