Currently working on a third party developed application, that does user management from within the application. Nothing special, you'd say.
Nope, it is not. But for the part, where a new user needs to get privileges to add other users... The query used to check whether a user can administer application users is a join over the view FLOWS_030100.APEX_WORKSPACE_APEX_USERS, specifically the column IS_ADMIN. Now, promoting an APEX application user to administrator is simple in the developer environment. In a runtime only environment, it is a little bit harder.
In fact, I can install new versions of the application by logging on to the database with SQL*Plus as workspace owner, and run the script. One of the first things this script does is:
begin wwv_flow_api.set_security_group_id(
p_security_group_id => APEX_UTIL.FIND_SECURITY_GROUP_ID('NAME'));
end;
/
After that, it continues with the creation of the flow (after the flow was dropped):
wwv_flow_api.create_flow(
p_id => 100,
p_display_id=> 100,
p_owner => 'OWNER',
p_name => 'NAME',
... etc...
However, owner corresponds with an Oracle database account, and there's a policy to have different application accounts from the database accounts. So in the workspace import (wwv_flow_fnd_user_api.create_company), there's a difference between Workspace schema (p_company_schemas) and provisioning schema (p_first_schema_provisioned) on one side, and the Workspace Administrator (wwv_flow_fnd_user_api.create_fnd_user) on the other.
As stated: I can log on to the database as owner (Oracle account) and install the application. However, what I cannot do is:
begin wwv_flow_api.set_security_group_id(
p_security_group_id => APEX_UTIL.FIND_SECURITY_GROUP_ID('NAME'));
end;
/
begin
apex_util.edit_user(
p_user_id => apex_util.get_user_id('APP_ADMIN'),
p_user_name => 'APP_ADMIN',
p_developer_privs => 'ADMIN:');
end;
/
It simply results in:
ERROR at line 1:
ORA-20001: User requires ADMIN privilege to perform this operation.
ORA-06512: at "FLOWS_030100.WWV_FLOW_FND_USER_API", line 76
ORA-06512: at "FLOWS_030100.WWV_FLOW_FND_USER_API", line 1192
ORA-06512: at "FLOWS_030100.HTMLDB_UTIL", line 756
ORA-06512: at line 2
The workaround is to execute this code as SYS (SYSTEM should work, too).
I suspect is is to do with the fact, the Workspace Administrator name differs from the owner of the application. Or, in technical terms, FLOWS_030100.APEX_WORKSPACE_APEX_USERS.USER_NAME does not correspond with the pseudo column USER.
