OK, there's no hard-fast rule here. You should grant the minimum required for your parsing schema.
However, I did a test on APEX 22.1 where I created a new workspace and new schema in APEX, then reported back the grants that the user had acquired using this script.
I then reversed the results into a script to grant roles & included a few optional/suggested ones too.
Script
Script provided for educational purposes only below. However, you can download this script with SQL\Plus parameters (which also creates the user) [here](raw.githubusercontent.com/Pretius/pretius-a..).*
-- Created by APEX
GRANT CREATE CLUSTER TO <the_user>;
GRANT CREATE DIMENSION TO <the_user>;
GRANT CREATE INDEXTYPE TO <the_user>;
GRANT CREATE JOB TO <the_user>;
GRANT CREATE MATERIALIZED VIEW TO <the_user>;
GRANT CREATE OPERATOR TO <the_user>;
GRANT CREATE PROCEDURE TO <the_user>;
GRANT CREATE SEQUENCE TO <the_user>;
GRANT CREATE SESSION TO <the_user>;
GRANT CREATE SYNONYM TO <the_user>;
GRANT CREATE PUBLIC SYNONYM TO <the_user>;
GRANT CREATE TABLE TO <the_user>;
GRANT CREATE TRIGGER TO <the_user>;
GRANT CREATE TYPE TO <the_user>;
GRANT CREATE VIEW TO <the_user>;
-- Additional/Recommended
GRANT CONNECT TO <the_user>;
GRANT RESOURCE TO <the_user>;
-- Removed recently by APEX Team ( likely for security reasons) required for Logger
GRANT CREATE ANY CONTEXT TO <the_user>;
-- Default Tablespace fix
DECLARE
v_username VARCHAR2(100) := UPPER('<the_user>');
v_tablespace VARCHAR2(100);
BEGIN
-- Get default tablespace for user
SELECT default_tablespace INTO v_tablespace FROM dba_users WHERE username = v_username;
-- Grant unlimited quota on tablespace to user
EXECUTE IMMEDIATE 'ALTER USER ' || v_username || ' QUOTA UNLIMITED ON ' || v_tablespace;
END;
/
-- Logger Installation fix (even if you have no plans to install logger)
GRANT SELECT ON sys.v_$parameter TO <the_user>;
Notes
The Data Tablespace fix
was a result of the error ...
ORA-01950: no privileges on tablespace 'DATA'
... which was resolved by this link.
The Logger Installation fix
was a result of the error ...
begin
partyp := dbms_utility.get_parameter_value('plsql_ccflags',intval, strval);
if strval is not null then
strval := ',' || strval;
end if;
:cur_plsql_ccflags := strval;
end;
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 140
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at line 7
01031. 00000 - "insufficient privileges"
... which was resolved by this link.