8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

APEX Tips : Create and Remove APEX Users Using the Command Line

This article demonstrates how to create and remove APEX users using the command line (SQL*Plus or SQLcl).

Setup
Create an APEX User
Remove an APEX User

Setup

We grant APEX_ADMINISTRATOR_ROLE to allow our database test user to manage APEX, including managing APEX users.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

grant apex_administrator_role to testuser1;

conn testuser1/testuser1@//localhost:1521/freepdb1

Create an APEX User

We check the schema for our workspace using the APEX_WORKSPACE_SCHEMAS view.

column workspace_name format a30
column schema format a30

select workspace_name, schema
from   apex_workspace_schemas
where  workspace_name = 'DEMO_WS'
order by 1;

WORKSPACE_NAME                 SCHEMA
------------------------------ ------------------------------
DEMO_WS                        DEMO_SCHEMA

SQL>

We display existing users using the APEX_WORKSPACE_APEX_USERS view.

column workspace_name format a30
column user_name format a30

select workspace_name,
       user_name
from   apex_workspace_apex_users
where  workspace_name = 'DEMO_WS'
order by 1, 2;

WORKSPACE_NAME                 USER_NAME
------------------------------ ------------------------------
DEMO_WS                        ADMIN

SQL>

The CREATE_USER procedure in the APEX_UTIL package allows us to create a user from the command line. There are a number of pieces of information we need to know in advance, in addition to basic user information.

Workspace : We use the workspace to get the security group ID, which we set, so the user is associated with the correct workspace.
Default Schema : The default schema for the user.
Developer Privileges : We use a colon-separated list of privileges to determine what the user can do. Including "ADMIN" makes the user an administrator.

The following example creates a user called "MYUSER" as an administrator of the "DEMO_WS" workspace.

declare
  l_workspace_id   number;
begin
  l_workspace_id := apex_util.find_security_group_id (p_workspace => 'DEMO_WS');
  apex_util.set_security_group_id (p_security_group_id => l_workspace_id);    
  apex_util.create_user(
    p_user_name       => 'MYUSER',
    p_first_name      => 'My First Name',
    p_last_name       => 'My Last Name',
    p_email_address   => 'me@example.com',
    p_default_schema  => 'DEMO_SCHEMA',
    p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
    p_web_password    => 'MyStrongPassword123');
  commit;
end;
/

Additional parameters can be set for the user. See the APEX_UTIL documentation for details.

We can see the user has been created.

column workspace_name format a30
column user_name format a30

select workspace_name,
       user_name
from   apex_workspace_apex_users
where  workspace_name = 'DEMO_WS'
order by 1, 2;

WORKSPACE_NAME                 USER_NAME
------------------------------ ------------------------------
DEMO_WS                        ADMIN
DEMO_WS                        MYUSER

SQL>

Remove an APEX User

The REMOVE_USER procedure in the APEX_UTIL package allows us to remove a user from the command line. Once again we need to set the security group ID so the user is removed from the correct workspace.

declare
  l_workspace_id   number;
begin
  l_workspace_id := apex_util.find_security_group_id (p_workspace => 'DEMO_WS');
  apex_util.set_security_group_id (p_security_group_id => l_workspace_id);    
  apex_util.remove_user(p_user_name => 'MYUSER');
  commit;
end;
/

We can see the user has been removed.

column workspace_name format a30
column user_name format a30

select workspace_name,
       user_name
from   apex_workspace_apex_users
where  workspace_name = 'DEMO_WS'
order by 1, 2;

WORKSPACE_NAME                 USER_NAME
------------------------------ ------------------------------
DEMO_WS                        ADMIN

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.