8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 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

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.