create apex user via plsql script

Question

Run script from https://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql and get error

[Error] Execution (1: 1): ORA-20987: APEX - Application ID and current security group ID are not consistent. - Contact your application administrator.
Details about this incident are available via debug id "78243".
ORA-06512: at "APEX_190100.WWV_FLOW_ERROR", line 1381
ORA-06512: at "APEX_190100.WWV_FLOW_ERROR", line 1416
ORA-06512: at "APEX_190100.WWV_FLOW_CUSTOM_AUTH_STD", line 724
ORA-06512: at "APEX_190100.WWV_FLOW_CUSTOM_AUTH_STD", line 588
ORA-06512: at "APEX_190100.HTMLDB_CUSTOM_AUTH", line 260
ORA-06512: at "USER.SP_CREATE_APEX_SESSION", line 30
ORA-06512: at line 2

error raise from

apex_custom_auth.post_login(
    p_uname => p_app_user,
    p_session_id => APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID,
    p_app_page => apex_application.g_flow_id||':'||p_app_page_id);

how to properly create a user in need wokrspace? Apex version 19.1

and create user

BEGIN
    FOR C1 IN (SELECT WORKSPACE_ID
                 FROM APEX_APPLICATIONS
                WHERE APPLICATION_ID = 4050)
    LOOP
        APEX_UTIL.SET_SECURITY_GROUP_ID (
            P_SECURITY_GROUP_ID   => C1.WORKSPACE_ID);
    END LOOP;
    
    APEX_UTIL.CREATE_USER (
        P_USER_NAME                 => 'BOB',
        P_EMAIL_ADDRESS             => '[email protected]',
        P_DEFAULT_SCHEMA            => 'MY_SPACE',
        P_ALLOW_ACCESS_TO_SCHEMAS   => 'MY_SPACE',
        P_WEB_PASSWORD              => 'change_me',
        P_DEVELOPER_PRIVS           =>
            'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'); -- workspace administrator

    COMMIT;
END;

i have same error

[Error] Execution (2: 1): ORA-20987: APEX - Security Group ID (your workspace identity) is invalid. - Contact your application administrator.

declare
    l_workspace_id      number;
begin
    l_workspace_id := apex_util.find_security_group_id (p_workspace => 'INTERNAL');
    apex_util.set_security_group_id (p_security_group_id => l_workspace_id);    
    apex_util.create_user(
        p_user_name    => 'NEWUSER1',
        p_web_password => 'secret99');
    commit;
end;

Error at line 1 ORA-20001: Package variable g_security_group_id must be set. ORA-06512: at "APEX_190100.WWV_FLOW_API", line 1828 ORA-06512: at "APEX_190100.WWV_FLOW_API", line 1863 ORA-06512: at "APEX_190100.WWV_FLOW_FND_USER_INT", line 1977 ORA-06512: at "APEX_190100.HTMLDB_UTIL", line 1452 ORA-06512: at line 6

SELECT WORKSPACE,WORKSPACE_DISPLAY_NAME, OWNER, APPLICATION_GROUP_ID  FROM APEX_APPLICATIONS WHERE WORKSPACE = 'INTERNAL' AND APPLICATION_ID = 4050;

INTERNAL >INTERNAL
APEX_190100
9.1051E+16 1 row selected.

Are you trying to create a new user or trying to create a session for an existing user? Your question implies the first, but your code is about the second. — pmdba
– pmdba, Commented Nov 13, 2021 at 11:52

Koen Lostrie · Accepted Answer · 2021-11-14 09:35:04Z

3

Ok, now this makes more sense. You're trying to create a user but it fails when trying to create a session prior to creating the user. You don't need a session to create a user, you just need the security group to be set. This can be done using APEX_UTIL.SET_SECURITY_GROUP_ID. I executed this code in my environment, connected via sqldeveloper:

DECLARE
    l_workspace_id      number;
BEGIN
    l_workspace_id := apex_util.find_security_group_id (p_workspace => '<myworkspacename>');
    apex_util.set_security_group_id (p_security_group_id => l_workspace_id);    
    APEX_UTIL.CREATE_USER(
        p_user_name    => 'NEWUSER1',
        p_web_password => 'secret99');
    COMMIT;
END;
/

and the user was created without errors.

answered Nov 14, 2021 at 9:35

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

ss_beer Over a year ago

and i have: [Error] Execution (1: 1): ORA-20001: Package variable g_security_group_id must be set. ORA-06512: at "APEX_190100.WWV_FLOW_API", line 1828 ORA-06512: at "APEX_190100.WWV_FLOW_API", line 1863 ORA-06512: at "APEX_190100.WWV_FLOW_FND_USER_INT", line 1977 ORA-06512: at "APEX_190100.HTMLDB_UTIL", line 1452 ORA-06512: at line 11

Koen Lostrie Over a year ago

did you replace the <myworkspacename> with your workspace name ?

Koen Lostrie Over a year ago

It's hard to diagnose what you're getting. You never say exactly what you are executing...

ss_beer Over a year ago

update post with my code

Koen Lostrie Over a year ago

It needs to be a schema that is tied to a workspace, but not internal. In the INTERNAL workspace you create another workspace, which you can link to a new or an existing schema. When logged into that schema you should be able to run this code. Don NOT use any schema created by apex (like APEX_019100 or APEX_PUBLIC_USER). I ran this code in the sql workshop on apex.oracle.com, in a test workspace, and it worked fine

|

Collectives™ on Stack Overflow

create apex user via plsql script

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related