Oracle Apex Upgrade to 4.2.3 fails with package Invalid in Apex_040200 scehma

Issue Description :
After upgrading the oracle APEX from 4.2.2 to 4.2.3 the Apex application did not start and throws package invalid errors:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00907: cannot load library unit APEX_040200.WWV_FLOW_SECURITY
         (referenced by APEX_040200.WWV_FLOW_PAGE)
0/0      PLS-00907: cannot load library unit APEX_040200.WWV_FLOW_SECURITY
         (referenced by APEX_040200.WWV_FLOW_DYNAMIC_EXEC)
0/0      PLS-00907: cannot load library unit APEX_040200.WWV_FLOW_SECURITY
         (referenced by APEX_040200.WWV_FLOW_DYNAMIC_EXEC)
0/0      PLS-00907: cannot load library unit APEX_040200.WWV_FLOW_SECURITY
         (referenced by APEX_040200.WWV_FLOW_DYNAMIC_EXEC)
Even UTLRP.sql does not help.

Solution:
The issue here is Missing grants on SYS and many other objects to APEX_040200 schema.

grant EXECUTE on SYS.OWA to APEX_040200;
grant EXECUTE on SYS.OWA_COOKIE to APEX_040200;
grant EXECUTE on SYS.OWA_CUSTOM to APEX_040200;
grant EXECUTE on SYS.OWA_UTIL to APEX_040200;
grant SELECT on SYS.USER$ to APEX_040200;
grant SELECT on SYS.V_$PARAMETER to APEX_040200;
grant SELECT on SYS.V_$OBJECT_DEPENDENCY to APEX_040200;
grant SELECT on SYS.V_$TIMER to APEX_040200;
grant SELECT on SYS.V_$VERSION to APEX_040200;
grant EXECUTE on SYS.WPG_DOCLOAD to APEX_040200;
grant SELECT on SYS.GV_$OPEN_CURSOR to APEX_040200;
grant SELECT on SYS.GV_$PROCESS to APEX_040200;
grant SELECT on SYS.GV_$SESSION to APEX_040200;
grant SELECT on SYS.GV_$SESSION_LONGOPS to APEX_040200;
grant SELECT on SYS.GV_$SESSION_WAIT to APEX_040200;
grant SELECT on SYS.GV_$SESSTAT to APEX_040200;
grant SELECT on SYS.GV_$SESS_IO to APEX_040200;
grant SELECT on SYS.GV_$SQL to APEX_040200;
grant SELECT on SYS.GV_$SQL_PLAN to APEX_040200;
grant SELECT on SYS.GV_$STATNAME to APEX_040200;
grant SELECT on SYS.GV_$SYSSTAT to APEX_040200;
grant SELECT on SYS.V_$DATABASE to APEX_040200;
grant SELECT on SYS.V_$DBLINK to APEX_040200;
grant SELECT on SYS.DBA_SEGMENTS to APEX_040200;
grant SELECT on SYS.DBA_SEQUENCES to APEX_040200;
grant SELECT on SYS.DBA_SNAPSHOTS to APEX_040200;
grant SELECT on SYS.DBA_SOURCE to APEX_040200;
grant SELECT on SYS.USER_SOURCE to APEX_040200;
grant SELECT on SYS.USER_SYS_PRIVS to APEX_040200;
grant SELECT on SYS.USER_TAB_COLUMNS to APEX_040200;
grant SELECT on SYS.GV_$INSTANCE to APEX_040200;
grant SELECT on SYS.GV_$LOCKED_OBJECT to APEX_040200;
grant SELECT on SYS.GV_$MYSTAT to APEX_040200;
grant SELECT on SYS.ALL_OBJECTS to APEX_040200;
grant SELECT on SYS.ALL_SCHEDULER_JOBS to APEX_040200;
grant SELECT on SYS.DBA_INDEXES to APEX_040200;
grant SELECT on SYS.DBA_IND_COLUMNS to APEX_040200;
grant SELECT on SYS.DBA_IND_EXPRESSIONS to APEX_040200;
grant SELECT on SYS.DBA_JOBS to APEX_040200;
grant SELECT on SYS.DBA_LOCK to APEX_040200;
grant SELECT on SYS.DBA_OBJECTS to APEX_040200;
grant SELECT on SYS.DBA_PROCEDURES to APEX_040200;
grant SELECT on SYS.DBA_PROFILES to APEX_040200;
grant EXECUTE on SYS.DBMS_RANDOM to APEX_040200;
grant EXECUTE on SYS.DBMS_REGISTRY to APEX_040200;
grant SELECT on SYS.SEG$ to APEX_040200;
grant SELECT on SYS.DBA_ERRORS to APEX_040200;
grant SELECT on SYS.DBA_EXTENTS to APEX_040200;
grant SELECT on SYS.DBA_FEATURE_USAGE_STATISTICS to APEX_040200;
grant SELECT on SYS.DBA_FREE_SPACE to APEX_040200;
grant EXECUTE on SYS.WWV_DBMS_SQL to APEX_040200;
grant SELECT on SYS.WWV_FLOW_GV$SESSION to APEX_040200;
grant EXECUTE on SYS.WWV_FLOW_KEY to APEX_040200;
grant EXECUTE on SYS.WWV_FLOW_VAL to APEX_040200;
grant SELECT on SYS.USER_OBJECTS to APEX_040200;
grant SELECT on SYS.USER_ROLE_PRIVS to APEX_040200;
grant SELECT on SYS.V_$ENCRYPTION_WALLET to APEX_040200;
grant EXECUTE on SYS.DBMS_APPLICATION_INFO to APEX_040200;
grant EXECUTE on SYS.DBMS_SQL to APEX_040200;
grant EXECUTE on SYS.DBMS_STATS to APEX_040200;
grant EXECUTE on SYS.UTL_ENCODE to APEX_040200;
grant EXECUTE on SYS.UTL_HTTP to APEX_040200;
grant EXECUTE on SYS.UTL_LMS to APEX_040200;
grant EXECUTE on SYS.UTL_RAW to APEX_040200;
grant EXECUTE on SYS.KU$_DDL to APEX_040200;
grant EXECUTE on SYS.KU$_DDLS to APEX_040200;
grant SELECT on SYS.DBA_RECYCLEBIN to APEX_040200;
grant SELECT on SYS.DBA_ROLE_PRIVS to APEX_040200;
grant SELECT on SYS.DBA_ROLLBACK_SEGS to APEX_040200;
grant SELECT on SYS.DBA_RSRC_CONSUMER_GROUPS to APEX_040200;
grant SELECT on SYS.DBA_RSRC_CONSUMER_GROUP_PRIVS to APEX_040200;
grant SELECT on SYS.DBA_RSRC_PLAN_DIRECTIVES to APEX_040200;
grant SELECT on SYS.ALL_SYNONYMS to APEX_040200;
grant SELECT on SYS.ALL_TAB_COLUMNS to APEX_040200;
grant SELECT on SYS.ALL_TAB_COMMENTS to APEX_040200;
grant SELECT on SYS.ALL_USERS to APEX_040200;
grant SELECT on SYS.ALL_VIEWS to APEX_040200;
grant SELECT on SYS.DBA_COL_COMMENTS to APEX_040200;
grant SELECT on SYS.DBA_COL_PRIVS to APEX_040200;
grant SELECT on SYS.DBA_CONSTRAINTS to APEX_040200;
grant SELECT on SYS.DBA_CONS_COLUMNS to APEX_040200;
grant SELECT on SYS.DBA_DATA_FILES to APEX_040200;
grant SELECT on SYS.DBA_DB_LINKS to APEX_040200;
grant SELECT on SYS.DBA_DEPENDENCIES to APEX_040200;
grant SELECT on SYS.DBA_SYNONYMS to APEX_040200;
grant SELECT on SYS.DBA_SYS_PRIVS to APEX_040200;
grant SELECT on SYS.DBA_TABLES to APEX_040200;
grant SELECT on SYS.DBA_TABLESPACES to APEX_040200;
grant SELECT on SYS.DBA_TAB_COLUMNS to APEX_040200;
grant SELECT on SYS.DBA_TAB_PRIVS to APEX_040200;
grant SELECT on SYS.DBA_TRIGGERS to APEX_040200;
grant SELECT on SYS.DBA_TRIGGER_COLS to APEX_040200;
grant SELECT on SYS.DBA_TS_QUOTAS to APEX_040200;
grant SELECT on SYS.DBA_TYPES to APEX_040200;
grant SELECT on SYS.DBA_USERS to APEX_040200;
grant SELECT on SYS.DBA_VIEWS to APEX_040200;
grant EXECUTE on SYS.DBMS_SCHEDULER to APEX_040200;
grant EXECUTE on SYS.DBMS_SESSION to APEX_040200;
grant EXECUTE on SYS.DBMS_TYPES to APEX_040200;
grant EXECUTE on SYS.DBMS_UTILITY to APEX_040200;
grant EXECUTE on SYS.DBMS_XMLGEN to APEX_040200;
grant EXECUTE on SYS.DBMS_ASSERT to APEX_040200;
grant EXECUTE on SYS.DBMS_CRYPTO to APEX_040200;
grant EXECUTE on SYS.DBMS_DB_VERSION to APEX_040200;
grant SELECT on SYS.OBJ$ to APEX_040200;
grant EXECUTE on SYS.DBMS_JOB to APEX_040200;
grant EXECUTE on SYS.DBMS_LDAP to APEX_040200;
grant EXECUTE on SYS.DBMS_LDAP_UTL to APEX_040200;
grant EXECUTE on SYS.DBMS_LOB to APEX_040200;
grant EXECUTE on SYS.DBMS_LOCK to APEX_040200;
grant EXECUTE on SYS.DBMS_METADATA to APEX_040200;
grant EXECUTE on SYS.DBMS_OBFUSCATION_TOOLKIT to APEX_040200;
grant EXECUTE on SYS.DBMS_OUTPUT to APEX_040200;
grant EXECUTE on SYS.DBMS_FLASHBACK to APEX_040200;
grant SELECT on SYS.ARGUMENT$ to APEX_040200;
grant SELECT on SYS.ALL_DEPENDENCIES to APEX_040200;
grant SELECT on SYS.ALL_COL_COMMENTS to APEX_040200;
grant SELECT on SYS.ALL_CONSTRAINTS to APEX_040200;
grant EXECUTE on SYS.HTF to APEX_040200;
grant EXECUTE on SYS.HTP to APEX_040200;
grant SELECT on SYS.USER_CONSTRAINTS to APEX_040200;
grant SELECT on SYS.NLS_DATABASE_PARAMETERS to APEX_040200;
grant SELECT on SYS.NLS_SESSION_PARAMETERS to APEX_040200;
grant SELECT on SYS.TS$ to APEX_040200;
grant SELECT on SYS.GV_$BACKUP to APEX_040200;
grant SELECT on SYS.DBA_ARGUMENTS to APEX_040200;
grant FLASHBACK on SYS.DUAL to APEX_040200;
grant EXECUTE on SYS.UTL_SMTP to APEX_040200;
grant EXECUTE on SYS.UTL_URL to APEX_040200;
grant ALTER on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant DELETE on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant INDEX on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant INSERT on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant SELECT on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant UPDATE on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant REFERENCES on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant ON COMMIT REFRESH on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant QUERY REWRITE on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant FLASHBACK on FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ to APEX_040200;
grant EXECUTE on XDB.DBMS_XMLDOM to APEX_040200;
grant EXECUTE on XDB.DBMS_XMLPARSER to APEX_040200;
grant EXECUTE on FLOWS_030100.WWV_FLOWS_VERSION to APEX_040200;

The above grants should resolve the issue. Even if some of the above grants fails with object does not exist errors, its okay.

- saket goenka

Role Refresher script

-- This is a dynamic role refresher script
-- Based on the role name you provide (user_SROLE,user_IROLE,user_DROLE,user_UROLE)
-- This code will create a procedure
-- When you execute this procedure it will refresh the role with the type of privilege selected, like SROLE is for SELECT role.


set lines 200 PAGES 50000 LONG 2000000
set serveroutput on
set define off
grant select on sys.dba_tables to user;
create or replace procedure user.refresh_role (role_name varchar2)
is
schema_name varchar2(30);
schema_check varchar2(30);
table_name varchar2(30);
sql_stmt varchar2(100);
role_flag char(1);
privilege_name varchar2(6);
CURSOR TABLE_LIST IS SELECT TABLE_NAME FROM sys.DBA_TABLES WHERE OWNER = schema_name;
begin
SELECT substr(upper(role_name),1,(instr(upper(role_name),'_',1,1))-1) INTO schema_name from dual;
select DISTINCT owner into schema_check from dba_tables where owner=schema_name;
if sql%notfound then
RAISE_APPLICATION_ERROR
(-20002, 'Schema not found');
end if;
SELECT substr(upper(role_name),(instr(upper(role_name),'_',1,1))+1,1) INTO role_flag from dual;
if upper(role_flag)='S' then
privilege_name := 'SELECT';
elsif upper(role_flag)='I' then
privilege_name := 'INSERT';
elsif upper(role_flag)='U' then
privilege_name := 'UPDATE';
elsif upper(role_flag)='D' then
privilege_name := 'DELETE';
ELSE
RAISE_APPLICATION_ERROR
(-20001, 'Incorrect Role Entered or Role doesnot match script format');
end if;
dbms_output.put_line('Role Name is:'||role_name);
dbms_output.put_line('Schema Name is:'||schema_name);
dbms_output.put_line('Privilege is:'||privilege_name);
open TABLE_LIST;
loop
fetch TABLE_LIST into table_name;
sql_stmt:= 'GRANT '||privilege_name||' ON '||schema_name||'.'||table_name||' to '||role_name;
EXECUTE IMMEDIATE sql_stmt;
exit when TABLE_LIST%notfound;
end loop;
close TABLE_LIST;
dbms_output.put_line('Role refresh completed successfully');
end;
/

-- This is how you execute the procedure.
-- exec user.refresh_role('user_SROLE');

Automatic Role creator script

-- This is a dynamic role creator script
-- Based on the schema name you provide and the role type (SELECT or INSERT or DELETE or UPDATE)
-- This code will create a procedure
-- When you execute this procedure it will create a role and grant the type of privilege mentioned for all the tables to that role.
set lines 200 PAGES 50000 LONG 2000000
set serveroutput on
set define off
grant select on sys.dba_tables to user;

create or replace procedure user.create_role (schema_name varchar2, role_type varchar2)
is
role_name varchar2(50) ;
table_name varchar2(30);
sql_stmt varchar2(100);
CURSOR TABLE_LIST IS SELECT TABLE_NAME FROM sys.DBA_TABLES WHERE OWNER = schema_name;
begin
if upper(role_type)='SELECT' then
role_name := upper(schema_name)||'_SROLE';
elsif upper(role_type)='INSERT' then
role_name := upper(schema_name)||'_IROLE';
elsif upper(role_type)='UPDATE' then
role_name := upper(schema_name)||'_UROLE';
elsif upper(role_type)='DELETE' then
role_name := upper(schema_name)||'_DROLE';
ELSE
RAISE_APPLICATION_ERROR
(-20001, 'Incorrect Role Type Entered');
end if;
dbms_output.put_line('Role Name is:'||role_name);
sql_stmt:= 'create role '||role_name;
EXECUTE IMMEDIATE sql_stmt;
dbms_output.put_line('Role Created');
open TABLE_LIST;
loop
fetch TABLE_LIST into table_name;
dbms_output.put_line('GRANTING '||role_type||' ON '||schema_name||'.'||table_name||' TO '||table_name||' ...');
sql_stmt:= 'GRANT '||role_type||' ON '||schema_name||'.'||table_name||' to '||role_name;
EXECUTE IMMEDIATE sql_stmt;
exit when TABLE_LIST%notfound;
end loop;
close TABLE_LIST;
dbms_output.put_line('All grants completed successfully');
end;
/

-- This is how you execute the procedure.
-- exec user.create_role('user','SELECT');

This is a Test Blog message

This is a Test Blog message