FLASHBACK Error on SYS.DUAL When Patching E-Business Suite 12.2 and APEX (Doc ID 2729531.1)

Last updated on FEBRUARY 09, 2023

Applies to:

Goal

APEX has been installed into the EBS database. It has been discovered that when patching EBS, the adop patching is generating warnings:
[WARNING] SYS.DUAL table has incorrect privileges
Shut down the application tier services and then run the following commands in the order shown
sqlplus apps/<APPS_Password> @ $AD_TOP/patch/115/sql/ADFIXUSER.sql
sqlplus apps/<APPS_Password> @ $AD_TOP/sql/adutlrcmp.sql
the script $AD_TOP/patch/115/sql/ADFIXUSER.sql copntains
sys.ad_zd_sys.fix_sysuser;
the procedure fix_sysuser simply revokes any not selec privilages against dual
procedure FIX_SYSUSER is
C_MODULE varchar2(80) := 'fix_sys_user';
L_SYS_USER varchar2(20) := 'SYS';
L_STMT varchar2(400);
v_priv varchar2(128);
v_grantee varchar2(128);
cursor SYS_USER is
select grantee, privilege
from dba_tab_privs
where table_name = 'DUAL'
and owner = l_sys_user
and grantor = l_sys_user
and privilege <> 'SELECT';
begin
log(c_module, 'EVENT', 'Revoke permission on DUAL');
for user in sys_user loop

v_grantee := sys.dbms_assert.enquote_name(user.grantee, FALSE);

log(c_module, 'STATEMENT', 'SQL: '||l_stmt);
l_stmt := 'revoke '||user.privilege||' on DUAL from '||v_grantee;
execute immediate l_stmt;
end loop;
end fix_sysuser;

The following query shows that FLASHBACK privileges have been granted to the APEX_NNNNNN user. The example below shows the results related to APEX 20.1:

It seems that during the install the flashback privilege was granted on sys.dual which absolutly no sense as dual should never be touched any form or shape
select * from dba_tab_privs where table_name='DUAL' and grantee='APEX_200100'
APEX_200100 SYS DUAL SYS SELECT
APEX_200100 SYS DUAL SYS FLASHBACK

Can these privileges be revoked?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.