Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

APEX_UTIL.CREATE_USER - User requires ADMIN privilege to perform this operation

KirstenSep 22 2017 — edited Feb 23 2018

Hi,

I want to use the procedure "apex_util.create_user" from an application with custom authentication, so the user doesn't have admin-privileges ( I'm using APEX 5.1.1, ORDS).

(Same as in APEX_UTIL.CREATE_USER - User requires ADMIN privilege to perform this operation )

I looked at 

https://community.oracle.com/message/13017524#13017524

https://community.oracle.com/thread/3956804

https://community.oracle.com/message/13570160#13570160

I have set "Modify Workspace Repository" in Security Attributes -> Database Session -> Runtime API Usage.

I created a procedure which calls a dbms_schedule job.

I set the APEX_ADMINISTRATOR_ROLE to my schema which is the primary schema of my workspace.

Nevertheless I get the message “ORA-20987: APEX - User APEX_PUBLIC_USER requires ADMIN privilege”.

So what do I miss?

 

CREATE OR REPLACE PACKAGE test_pkg IS

procedure start_job(p_username in varchar2);

procedure create_user(

p_workspace        in apex_workspace_apex_users.workspace_name%type,

p_username         in apex_workspace_apex_users.user_name%type,

p_new_password     in varchar2,

p_change_on_use    in varchar2 default 'N');

END test_pkg;

/

CREATE OR REPLACE PACKAGE BODY test_pkg IS

procedure start_job(p_username in varchar2) as

l_jobname  varchar2(255) := 'testjob';

l_user_name varchar2(256) := p_username;

begin

l_jobname := dbms_scheduler.generate_job_name(l_jobname);

     dbms_scheduler.create_job(

        job_name            => l_jobname,

        job_type            => 'PLSQL_BLOCK',

        job_action          => 'BEGIN test_pkg.create_user(''myschema'',''' || l_user_name || ''',''' || l_user_name || '''); END;',

        enabled             => true

);

end;

procedure create_user(

p_workspace        in apex_workspace_apex_users.workspace_name%type,

p_username         in apex_workspace_apex_users.user_name%type,

p_new_password     in varchar2,

p_change_on_use    in varchar2 default 'N'

) is

l_workspace_id apex_workspace_apex_users.workspace_id%type;

begin

begin

select workspace_id into l_workspace_id

from apex_workspaces where workspace = p_workspace;

exception

when NO_DATA_FOUND then

      raise_application_error(-20000, 'NONEXISTENT WORKSPACE', true);

end;

apex_util.set_security_group_id(

p_security_group_id=>l_workspace_id

);

begin

      APEX_UTIL.CREATE_USER(

        P_USER_NAME    => p_username,

        P_WEB_PASSWORD => p_new_password);

exception when others then      

        debug (sqlerrm);

end;

end create_user;

END test_pkg;

/

Thanks a lot for responding.

Kirsten

This post has been answered by Scott on Sep 26 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2017
Added on Sep 22 2017
5 comments
5,679 views