Oracle DB Schema Grants required for APEX

Oracle DB Schema Grants required for APEX

A suitable schema for APEX

Matt Mulvaney's photo
Matt Mulvaney
·

2 min read

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.

orclapex