Friday, February 13, 2009

APEX V3.1.2 Runtime Adminitration

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'));

After that, it continues with the creation of the flow (after the flow was dropped):

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'));
p_user_id => apex_util.get_user_id('APP_ADMIN'),
p_user_name => 'APP_ADMIN',
p_developer_privs => 'ADMIN:');

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.


Dik Pater said...

How does this work in apex 425 ??

Frank said...

Dik, this is an APEX 3.1.2 blog entry, the current production APEX is V5.0... As you ask about 4.2.5, which is not the terminal release, I advise to go to 4.2.6 first.
Having said that, I installed using, after which I ran the script, found on The result is:
SQL> @cre_env
Enter the short APEX applicaton name: DEF
...Creating the APEX tablespace...
Tablespace created.

...Creating user and granting rights...
Enter value for apex_app_db_password: dpater
Grant succeeded.
Grant succeeded.
User altered.
User altered.

...Creating APEX Workspace DEF
PL/SQL procedure successfully completed.

...Creating APEX Workspace administrator account...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

Commit complete.
So, I say it still seems to work - at least, I get no errors, running the lot connected as SYSDBA.
The REMOVE_WORKSPACE works as well, but you will have to "drop tablespace including contents and datafiles" yourself.

I will try to install some http server and test the actual login as DEF_ADMIN, unless you feel this is proof enough.