DBA Hub

📋Steps in this guide1/3

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).

oracle miscconfigurationintermediate
by OracleDba
19 views
1

Setup

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

Code/Command (click line numbers to comment):

1
2
3
4
5
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

grant apex_administrator_role to testuser1;

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

Create an APEX User

We check the schema for our workspace using the view. We display existing users using the view. The procedure in the 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. Additional parameters can be set for the user. See the APEX_UTIL documentation for details. We can see the user has been created.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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>

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>

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   => '[email protected]',
    p_default_schema  => 'DEMO_SCHEMA',
    p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
    p_web_password    => 'MyStrongPassword123');
  commit;
end;
/

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>
3

Remove an APEX User

The procedure in the 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. We can see the user has been removed. For more information see: - APEX_UTIL - APEX Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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;
/

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!